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

Rheumatism

Answer 1

You're right, it seems that you cannot use DISTINCT with list. Since you don't need id but only the names, you can use find all like above and then $first_names = Set::extract($first_names, '/Person/first_name');. That will give you a array with distinct first names.

Saxon

Not only did this work perfectly for the list but I have used set commands in other places also. Thank you for sharing about this powerful library! – Rheumatism

I tried this, but I still find duplicates in the resulting list. – Increment

@Increment you can remove duplicates from the extracted array with array_unique. And while you're at it, take a look at the Hash class (since CakePHP 2.2) which will eventually replace Set. – Ammonic

@koiyu That's true but the answer provided did not remove duplicates which was the question stated. (The function of DISTINCT) – Increment

@Pyrite it doesn't seem like it has been removed book.cakephp.org/3.0/en/core-libraries/hash.html – Nina

@ele I believe I must have got confused; the Set class was removed in 3.0 (which was the predecessor to the Hash class). – Expectation

Answer 2

Try to use 'group by', it works perfectry:

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

Va

This works but has the unintended consequence of sorting the list by first_name which may not be desirable. – Increment

@Increment I also have this problem. Is there a solution for the order problem? – Foam

Answer 3

Here's how I did it in CakePHP 3.x:

     $query = $this->MyTables->find('all');
     $result = $query->distinct()->toArray();

Firstly

Answer 4

You can try this. Here this takes Person id as key, so there is no chance for duplicate entries.

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

Rhone

Answer 5

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);

Negron

Answer 6

I know it is a question for CakePHP 1.2, but I was searching for that too with CakePHP version 3. And in this version there is a method to form the Query into a distinct one:

$first_names = $this->Persons->find(
    'list',
    [
        'fields'=> ['name']
    ]
)
  ->distinct();
;

This will generate a sql query like this:

SELECT DISTINCT Persons.name AS `Persons__name` FROM persons Persons

But the distinct method is a bit mightier than just inserting DISTINCT in the query.

If you want to just distinct the result on one field, so just throwing away a row with a duplicated name, you can also use the distinct method with an array of the fields as parameter:

$first_names = $this->Persons->find(
    'list',
    [
        'fields'=> ['id', 'name'] //it also works if the field isn't in the selection
    ]
)
  ->distinct(['name']); //when you use more tables in the query, use: 'Persons.name'
;

This will general a sql query like this (for sure it is a group query):

SELECT DISTINCT
    Persons.id AS `Persons__id`, Persons.name AS `Persons__name`
FROM persons Persons
GROUP BY name #respectively Persons.name

Hope I will help some for CakePHP 3. :)

Apopemptic

Answer 7

Yes the problem is that you are using a listing which designed for a id / value output. You probably will have to do a find('all') and then build the list yourself.

Looper

Answer 8

Yes I also tried to fetch unique results with 'list' but its not working. Then I fixed the problem by using 'all'.

Wilkerson

Answer 9

In the example now on the book for version 2 it states the following:

public function some_function() {
    // ...
    $justusernames = $this->Article->User->find('list', array(
        'fields' => array('User.username')
    ));
    $usernameMap = $this->Article->User->find('list', array(
        'fields' => array('User.username', 'User.first_name')
    ));
    $usernameGroups = $this->Article->User->find('list', array(
        'fields' => array('User.username', 'User.first_name', 'User.group')
    ));
    // ...
}

With the above code example, the resultant vars would look something like this:

$justusernames = Array
(
    //[id] => 'username',
    [213] => 'AD7six',
    [25] => '_psychic_',
    [1] => 'PHPNut',
    [2] => 'gwoo',
    [400] => 'jperras',
)

$usernameMap = Array
(
    //[username] => 'firstname',
    ['AD7six'] => 'Andy',
    ['_psychic_'] => 'John',
    ['PHPNut'] => 'Larry',
    ['gwoo'] => 'Gwoo',
    ['jperras'] => 'Joël',
)

$usernameGroups = Array
(
    ['User'] => Array
    (
        ['PHPNut'] => 'Larry',
        ['gwoo'] => 'Gwoo',
    )

    ['Admin'] => Array
    (
        ['_psychic_'] => 'John',
        ['AD7six'] => 'Andy',
        ['jperras'] => 'Joël',
    )

)

You have to plan your query in a slightly different way and plan your database to accommodate a list find.

Wildlife

Answer 10

In some cases, you wish to group by using some key, but you want unique element within the results. For example, you have a calendar application with two types of events. One event on day 1rst and the other one on the 2nd day of month 1. And you want to show or rather count all the events, grouped by day and by type. If you use only DISTINCT, it is quite difficult. The simplest solution is to group twice:

$this->Events->virtualFields['count'] = 'COUNT(*)';                   
$acts  = $this->Activity->find('all',array(                   
             'group' => array('DAY(Event.start)','EventType.id'),
               ));

Prosimian

Answer 11

Just group by the fields that you want to get distinct.. or use Set::extract() and then array_unique()

Alvera

Answer 12

In 2.7-RC version, this is working

$this->Model1->find('list', array(
      'fields' => array('Model1.field1', Model1.field1')
));

Cristiano