Using DISTINCT in a CakePHP find function

Question

I am writing a CakePHP 1.2 app. I have a list of people that I want the user to be able to filter on different fields. For each filterable field, I have a drop down list. Choose the filter combination, click filter, and the page shows only the records that match.

In people_controller, I have this bit of code:

$first_names = $this->Person->find('list', array(
    'fields'=>'first_name',
    'order'=>'Person.first_name ASC',
    'conditions'=> array('Person.status'=>'1')
));
$this->set('first_names', $first_names);

(Status = 1 because I am using a soft delete.)

That creates an ordered list of all first_names. But duplicates are in there.

Digging around in the Cookbook, I found an example using the DISTINCT keyword and modified my code to use it.

$first_names = $this->Person->find('list', array(
    'fields'=>'DISTINCT first_name',
    'order'=>'Person.first_name ASC',
    'conditions'=> array('Person.status'=>'1')
));

This gives me an SQL error like this:

Query: SELECT `Person`.`id`, DISTINCT `Person`.` first_name` FROM `people` AS `Person`   WHERE `Person`.`status` = 1   ORDER BY `Person`.`first_name` ASC

The problem is obvious. The framework is adding Person.id to the query. I suspect this comes from using 'list'.

I will use the selected filter to create an SQL statement when the filter button is clicked. I don't need the is field, but can't get rid of it.

Thank you, Frank Luke

Answer 1

Using SQL grouping will also produce a distinct list. Not sure of the negative consequences if any, but it seems to work fine for me.

$first_names = $this->Person->find('list', array(
    'fields' => 'first_name',
    'order' => 'first_name',
    'group' => 'first_name',
    'conditions' => array('Person.status' => '1'),
));
$this->set('first_names', $first_names);