cakephp find('list') - problem using

Question

I'm trying to get an array to poplulate a Counties select. If I use find('all') I can get the data but this array needs flattening to use in the view with $form->input($counties).

If I use find('list') I can't seem to get the right array - which is simple array of county names.

What I have tried is this:

        $ops=array(
        'conditions' => array(
            'display' => '!=0',
            'TO_DAYS(event_finish) >= TO_DAYS(NOW())'
        ),
        'fields' => 'DISTINCT venue_county',
        'order' => 'venue_county DESC'
    );
    $this->set('counties', $this->Event->find('list',$ops));

but the SQL this generates is:

SELECT Event.id, DISTINCT Event.venue_county FROM events AS Event WHERE display = 1 AND TO_DAYS(event_finish) >= TO_DAYS(NOW()) ORDER BY venue_county DESC

which generates an error because it first inserts the Event.id field in the query - which is not wanted and causes the error.

In my database I have a single table for Events which includes the venue address and I don't really want to create another table for address.

What options should I be using efor the find('list') call?

Answer 1

the 'list' expect 2 fields - one for the ID and another for the Text label. If you use only one it get current Model ID for the IF field. Here is how it should look like:

$ops=array(
    'conditions' => array(
        'display !=' => 0,
        'TO_DAYS(event_finish) >= TO_DAYS(NOW())'
    ),
    'fields' => array('venue_county', 'venue_county'),
    'order' => 'venue_county DESC',
    'group'=>'venue_county'

);
$this->set('counties', $this->Event->find('list',$ops));

Bear in mind that conditions like ">", "!=", "LIKE", "IN" etc are placed from the left side (like in my example the display field).

Basically you can change the fields but first one need to be the ID one the next one need to be the TEXT one

Answer 2

try specifying the fields for the list

<?php
$this->set('counties', $this->Event->find('list',array(
    'conditions' => array(
        'NOT' => array('display' => 0),
        'TO_DAYS(event_finish) >= TO_DAYS(NOW())'
    ),
    'fields' => array('Event.venue_county','Event.venue_county'),
    'order' => 'venue_county DESC'
)));
?>

though I might recommend you do your date conditions natively (assuming that the [event_finish] field is a data or datetime field). and while you're at it you might as well simplify the display field condition to an IS vs. and ISN'T (I'd recommend making that field a type tinyint(1) so it's basically true/false).

<?php
$this->set('counties', $this->Event->find('list',array(
    'conditions' => array(
        'display' => 1,
        'Event.event_finish >' => date('Y-m-d 00:00:00'),
    ),
    'fields' => array('Event.venue_county','Event.venue_county'),
    'order' => 'venue_county DESC'
)));
?>