Add multiple count fields of contained model to query in cakephp 3

Question

I want to have in my model, instead of the complete set of entries of the models contained by another one, just the quantity of them. I could do this by adding the "size" field to the resultset, but I'd like to do this in the query, as I want to paginate and order the results dinamically. I am using this code, but for some reason, if the first count is different from zero, both count fields have the same value, which is the value of the second count field.

$query = $this->Users->find('all')
            ->contain(['Likes','Favs']);

 $query
      ->select(['like_count' => $query->func()->count('Likes.id')])
      ->leftJoinWith('Likes')
      ->group(['Users.id'])
      ->autoFields(true);

 $query
     ->select(['fav_count' => $query->func()->count('Favs.id')])
     ->leftJoinWith('Favs')
     ->group(['Users.id'])
     ->autoFields(true);

$this->paginate['sortWhitelist'] = [
            'name',
            'email',
            'last_login',
            'fav_count',
            'like_count',
        ];

I would like to know why this happens and if there is any other way to do what I attempt, which would be to have ('name', email, 'last_login', quantity of entries in Likes with the user's id, quantity of entries in Favs with the user's id). I have tried using join() to do the left join, but I haven't been able to obtain the result I want.

Answer 1

If you have multiple joins, then you need to count with DISTINCT, eg:

COUNT(DISTINCT Likes.id)

This is because your result will contain Likes * Favs number of rows, as for every joined like, all favs will be joined, ie for 2 likes and 10 favs you'd end up with 20 rows in total. A regular COUNT() would include every single one of those rows.

Also note that you don't need to repeat all that grouping, etc stuff, and you can use a callable for select() to avoid breaking up the builder.

$query = $this->Users
    ->find('all')
    ->select(function (\Cake\ORM\Query $query) {
        return [
            'like_count' => $query->func()->count(
                $query->func()->distinct(['Likes.id' => 'identifier'])
            ),
            'fav_count' => $query->func()->count(
                $query->func()->distinct(['Favs.id' => 'identifier'])
            ),
        ];
    })
    ->autoFields(true)
    ->contain(['Likes', 'Favs'])
    ->leftJoinWith('Likes')
    ->leftJoinWith('Favs')
    ->group(['Users.id']);

Using the functions builder to generate the DISTINCT is a workaround, as there is no API yet that would allow to specifically generate a keyword. The result will be something like DISTINCT(Likes.id), but it will work fine, the parentheses will be interpreted as part of the expression after the keyword, not as part of a function call.