Cakephp 2.x finds a query containing DISTINCT and COUNT together in one field

Question

User Table structure.

+------+-------+
|  id  | data  |
+------+-------+
|  1   |   a   |
+------+-------+
|  1   |   b   |
+------+-------+
|  2   |   c   |
+------+-------+

Desired result.

+------+-------+
|  id  | count |
+------+-------+
|  1   |   2   |
+------+-------+
|  2   |   1   |
+------+-------+

I tried DISTINCT and COUNT in different ways, but still did not get the desired result.

What am i trying

$this->User->find('all',array(
'conditions'=> array(),
'fields' => array(
'DISTINCT(User.id)',
'COUNT(DISTINCT User.id) as count'
)
)
);

Answer 1

Try the group by clause

$this->User->find('all',
    array(
        'fields' => array(
            'User.id',
            'COUNT(User.id) as count'
        ),
        'group' => 'User.id'
    )
);

Answer 2

In addition to Bill answer , to get countas a field User, you need to get it asvirtualField

$this->User->virtualFields['count'] = 0;

$this->User->find('all', [
    'fields' => array(
        'User.id',
        'COUNT(User.id) as User__count'
    ),
    'group' => 'User.id'
]);

This will be your result:

########## DEBUG ##########
array(
    (int) 0 => array(
        'User' => array(
            'id' => '1',
            'count' => '2'
        )
    ),
    (int) 1 => array(
        'User' => array(
            'id' => '2',
            'count' => '1'
        )
    )
)
###########################