CODEWITHSUNDEEP
mysqlcakephp-3.0
JPT
JPT

Reputation: 77

cakephp 3.0 + group by + count +paginate

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`

Upvotes: 2

Views: 4431

Answers (3)

Antoine Chaupin
Antoine Chaupin

Reputation: 1

After a lot of research, I come with a pretty good solution : CounterCache !

Everytime you add a new game to your user, the number of games game_count in user table is updated. It works and it's a faster solution.

Upvotes: 0

JPT
JPT

Reputation: 77

I am using below code for sort 

<th><?= $this->Paginator->sort('count_games_created','Number of games created') ?></th>
<th><?= $this->Paginator->sort('count_games_joined','Number of games attended') ?></th>

Upvotes: 0

Jun
Jun

Reputation: 660

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

Upvotes: 5

Related Questions