[FIXED] cakephp 3.0 + group by + count +paginate

Issue

I have one table user and another table games.

Now user will create games. And now I want list of user with his count of games he created.

Below query will output (IN PHPMYADMIN) exactly what I want. But I don't have idea how to fire such query in cakephp 3.0:

SELECT `users`.`id`,`firstname`,`lastname`,(select count(id) from games where games.created_by=users.id) FROm users group by `users`.`id`

Solution

In your controller :

$users = $this->Users->find('all', [
        'fields' => [
            'id' => 'Users.id',
            'firstname' => 'firstname',
            'lastname' => 'lastname',
            'count_games' => 'COUNT(games.id)'
        ],
        'join' => [
            'table' => 'games', 
            'type' => 'LEFT',
            'conditions' => 'games.created_by = Users.id'
        ],
        'group' => ['Users.id'],
]);
$this->set('users', $users);
$this->set('_serialize', ['users']);

Answered By - Jun