I have a database with Rounds and Users. Rounds belongsToMany Users and Users belongsToMany Rounds, so a many-to-many relation. A join table rounds_users was added to do this. EDIT: Used the incorrect phrase here. I meant 'belongsToMany' instead of 'hasMany'
Now I want to retrieve a list of Rounds, together with the number of linked Users per round.
In case of a one-to-many relation something like the following would work:
$rounds = $this->Rounds->find()
->contain(['Users' => function ($q)
{
return $q->select(['Users.id', 'number' => 'COUNT(Users.round_id)'])
->group(['Users.round_id']);
}
]);
...According to Count in contain Cakephp 3
However, in a many-to-many relation Users.round_id
does not exist. So, what could work instead?
Note: Several questions like this have been asked already, but few about CakePHP 3.x, so I still wanted to give this a try.
Note 2: I can get around this by using the PHP function count
, though I'd rather do it more elegantly
EDIT: As suggested below, manually joining seems to do the trick:
$rounds_new = $this->Rounds->find()
->select($this->Rounds)
->select(['user_count' => 'COUNT(Rounds.id)'])
->leftJoinWith('Users')
->group('Rounds.id');
...With one problem! Rounds without Users still get a user_count
equal to 1. What could be the problem?
hasMany
associations (instead ofbelongsToMany
), then you have aone Rounds to many RoundsUsers
relation, ie you could still use the "count in contain" example viaRoundsUsers
, don't you? – ndmCOUNT
usually doesn't countNULL
values, ie it should work with aLEFT
join, and that may actually be what the OP wants, ie retrieve all rounds, and a count of0
for rounds without users. If those0
user rounds are unwanted, then I'd recommend anINNER
join too. – ndmUsers
? – ndm