Filter query by count associated data (Cakephp 3)

Need Help
1

I want to do a query on “Articles” but I only want the “Articles” wich have 2 or more “Comments”. So I have to count “Comments” and use the count result on where clause.

I know the next code is wrong but it would be something like:

$articles = $this->Articles->find();

$articles->matching('Comments', function ($q) {
    $commentsCount = $q->func()->count('Comments.id');   
    return $q->where($commentsCount . ' >= ' => 2);
});

I can´t find any information about this.

Thanks for the help.

2

Not a direct answer but…

This might be a good time to consider the CounterCache behavior.

It’s possible your currently strategy could become very costly in the future. CounterCache would store the count of comments in the article and the query would then be trivial.

1 Like
3

Old post, but I came across the same problem, and it took me quite a while to figure this out. I solved it as follow:

$query = $this->Articles->find();
$query->select([
	'Articles.id',
    'comment_count' => $query->func()->count('Comments.id'),
]);
$query->leftJoinWith('Comments');
$query->group(['Comments.article_id']);
$query->having(['comment_count >=' => 2]);