Count in contain Cakephp 3

Question

I have a table Post and this has a has-many association with a table Stars.

I can get all the associated data using:

$this->Posts->find()->contain(['Stars']);

That works well.

But I want to count the Stars. I have tried this but its not working:

$this->Posts->find->contain([
    'Stars' => function($q) {
        return $q->select(['total' => $q->func()->count('Stars.post_id')]);
    }
]);

//I've also tried this
...
...$q->select(['total' => "COUNT(Stars.post_id)"]);
...
//Also fail

This does not return the number of associated Stars.

Is there something wrong or should do it some other way?

Thanks

Answer 1

you have to select also the foreign key otherwise cake is not able to join the tables. And you have also to group the result

'Stars' => function($q) {
    $q->select([
         'Stars.post_id',
         'total' => $q->func()->count('Stars.post_id')
    ])
    ->group(['Stars.post_id']);

    return $q;
}