This post is the answer to Mandy Singh’s comment to the article Select distinct with CakePHP in which he asked how to use “count” with “distinct”. The answer I gave then — to use a custom query — works, but there exists a more Cake-like solution.

The obvious solution — using findCount() — doesn’t work, as there is no way to specify that you want to use “distinct”.

So to get the desired result we have to use a find() or findAll() statement as shown in the following example:

$this->User->find(null, "COUNT(DISTINCT User.city) AS 'count'");