Using distinct and count with CakePHP

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