0

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?

Community
  • 1
  • 1
Roberto
  • 958
  • 13
  • 33
  • 1
    You can always join in associations instead of containing them (kinda like in your **[SUM() on ManyToMany question](http://stackoverflow.com/questions/38935419/cakephp-3-x-sum-on-manytomany)**)... besides that, if you've explicitly created `hasMany` associations (instead of `belongsToMany`), then you have a `one Rounds to many RoundsUsers` relation, ie you could still use the "count in contain" example via `RoundsUsers`, don't you? – ndm Feb 15 '17 at 17:02
  • @ndm Whoops, I used the wrong phrase. It really is many-to-many. Manually joining seems the way to go, I added an example. This does result into a new problem. – Roberto Feb 15 '17 at 17:10
  • Try using innerJoinWith instead of leftJoinWith ! – Manohar Khadka Feb 15 '17 at 17:29
  • @ManoharKhadka `COUNT` usually doesn't count `NULL` values, ie it should work with a `LEFT` join, and that may actually be what the OP wants, ie retrieve all rounds, and a count of `0` for rounds without users. If those `0` user rounds are unwanted, then I'd recommend an `INNER` join too. – ndm Feb 15 '17 at 17:38
  • That being said, wouldn't you want to count on `Users`? – ndm Feb 15 '17 at 17:39
  • @ManoharKhadka, @ndm, using `INNER` instead would indeed just leave out all the rows without users, which is not desired. I do want what you describe. @ndm I am not sure what you mean. I want to count Users, so I count the duplicates of otherwise unique Rounds, right? – Roberto Feb 16 '17 at 10:55

1 Answers1

3

Join in the association

As already mentioned in the comments, you can always join in associations instead of containing them (kinda like in your SUM() on ManyToMany question).

The reason why you retrieve a count of 1 for rounds that do not have any associated users, is that you are counting on the wrong table. Counting on Rounds will of course result in a count of at least 1, as there will always be at least 1 round, the round for which no associated users exist.

So long story short, count on Users instead:

$rounds = $this->Rounds
    ->find()
    ->select($this->Rounds)
    ->select(function (\Cake\ORM\Query $query) {
        return [
            'user_count' => $query->func()->count('Users.id')
        ];
    })
    ->leftJoinWith('Users')
    ->group('Rounds.id');

Counter cache for belongsToMany associations

There's also the counter cache behavior, it works for belongsToMany associations too, as long as they are set up to use a concrete join table class, which can be configured via the association configurations through option:

class RoundsTable extends Table
{
    public function initialize(array $config)
    {
        $this->belongsToMany('Users', [
            'through' => 'RoundsUsers'
        ]);
    }
}

class UsersTable extends Table
{
    public function initialize(array $config)
    {
        $this->belongsToMany('Rounds', [
            'through' => 'RoundsUsers'
        ]);
    }
}

The counter cache would be set up in the join table class, in this example RoundsUsersTable, which would have two belongsTo associations, one to Rounds and one to Users:

class RoundsUsersTable extends Table
{
    public function initialize(array $config)
    {
        $this->belongsTo('Rounds');
        $this->belongsTo('Users');

        $this->addBehavior('CounterCache', [
            'Rounds' => ['user_count']
        ]);
    }
}

Count in containment

If you would have actually explicitly created hasMany associations (instead of belongsToMany), then you would have a one Rounds to many RoundsUsers relation, ie you could still use the linked "count in contain" example via RoundsUsers.

However, that would leave you with a structure where the count would be placed in a nested entity, which in turn would be missing for rounds that do not have any associated users. I would imagine that in most situations this kind of structure would require reformatting, so it's probably not the best solution. However, for the sake of completion, here's an example:

$rounds = $this->Rounds
    ->find()
    ->contain(['RoundsUsers' => function (\Cake\ORM\Query $query) {
        return $query
            ->select(function (\Cake\ORM\Query $query) {
                return [
                    'RoundsUsers.round_id',
                    'number' => $query->func()->count('RoundsUsers.round_id')
                ];
            })
            ->group(['RoundsUsers.round_id']);
        }]
    );

See also

ndm
  • 59,784
  • 9
  • 71
  • 110