CakePHP 2.1 - How to properly use DISTINCT in find()

Question

I have a question which is driving me crazy and I have to admit I am not that experienced in CakePHP. As mentioned in this question, Using DISTINCT in a CakePHP find function, using DISTINCT this way:

$this->Model->find('all', array('fields'=>'DISTINCT field_name'));

does not return DISTINCT values, instead it returns all rows. In fact, the DISTINCT here is completely pointless because, for some reason , CakePHP adds TableName.idin the SQL query (why?? can I remove the id reference??), effectively returning every DISTINCT primary key (=all rows=unhelpful).

So, I still want to return the DISTINCT values of a particular field_name column. Can I not do it using just the find('all') or find('list') function? Is it really that the proper way of doing it using this Set::extract() function described in the link above? That appears to be a overly indirect solution by CakePHP, normally Cake make my life easier. :-) What is the proper way of using find and DISTINCT together? Maybe DISTINCT doesn't work for find()?

Looking at the CookBook, they say: "A quick example of doing a DISTINCT query. You can use other operators, such as MIN(), MAX(), etc., in a similar fashion:"

<?php
    array(
        'fields' => array('DISTINCT (User.name) AS my_column_name'),
        'order' = >array('User.id DESC')
    )
?>

Source: http://book.cakephp.org/2.0/en/models/retrieving-your-data.html

This indicates that DISTINCT should be possible to use, but what is what here? Does (User.name) correspond to the field_name I want DISTINCT for or is my_column_name my field_name?

Finally, has any of this changed when migrating from CakePHP 1.x to CakePHP 2.x? Ie are the answers for CakePHP 1.x seen on Stackoverflow still relevant?

Thanks in advance!

Solution 1

Yes, the second snippet is the correct way to do a SELECT DISTINCT in CakePHP 2.x. User.name corresponds to the field name, in this case to the field name in the users table. my_column_name is an (optional) alias for the field name in the result set, i.e. instead of name the field will be named my_column_name in the result set.

Solution 2

For CakePHP 3.X

To select distinct fields, you can use the distinct() method:

// Results in SELECT DISTINCT country FROM a table...
$query = $articles->find();
$query->select(['country'])
    ->distinct(['country']);

CookBook Documentation

Comments

[Admin]

Thanks for the answer. However, using $regions = $this->Provider->find('all', array ('fields' => array('DISTINCT (provider.region) AS region'))); or $regions = $this->Provider->find('all', array ('fields' => array('DISTINCT (provider.region)'))); it doesn't pick up unique entries. Instead all rows which is not DISTINCT and not what I wanted. The resulting SQL query: SELECT DISTINCT (provider.region), Provider.id` FROM carecrowd.providers AS Provider WHERE 1 = 1`. Did I missunderstand you somehow and is my code incorrect?

[dhofstet]

The generated SQL query is incorrect, there should be no Provider.id in the query. Which CakePHP version do you use? And which database? At least with CakePHP 2.1.3 and MySQL the correct query is generated.

[Admin]

Thanks for the reply. I have CakePHP 2.1.3 and MySQL. So that wasn't the issue. However, your reply led me to think about other things than faulty syntax, but related to the model. As it turns out, the problem was that I had established a model association for the Provider model, without the model association the above syntax works fine. So Cake added the Provider.id automatically to the SQL query, is it supposed to be that way or is it a bug? I am asking since the model association effectively blocks a DISTINCT find query for the relevant model. Or maybe I missed something?

[dhofstet]

Yes, CakePHP automatically retrieves data from associated models. To avoid that, you have to specify 'recursive' => -1 in the options array for the find (or use the Containable behavior).

[Joseph]

This is showing first on Google for searches for this issue so I just wanted to add something to help others: If you're doing a find('list') then Cake will keep the Model.id even if you use recursive = -1. An alternative way of removing duplicates in Cake is suggested at stackoverflow.com/a/1718614/794458

[Davorin]

Generates a GROUP BY for me, not DISTINCT (Cake 3.5)