# Filter query by count associated data (Cakephp 3)

**URL:** <https://discourse.cakephp.org/t/filter-query-by-count-associated-data-cakephp-3/7868>\
**Category:** Need Help\
**Tags:** help\
**Created:** [May 27, 2020, 10:35am UTC](https://discourse.cakephp.org/t/filter-query-by-count-associated-data-cakephp-3/7868 "2020-05-27T10:35:57Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![Jerog](https://avatars.discourse-cdn.com/v4/letter/j/58956e/32.png) [@Jerog](https://discourse.cakephp.org/u/Jerog)\
**Post date:** [May 27, 2020, 10:35am UTC](https://discourse.cakephp.org/t/filter-query-by-count-associated-data-cakephp-3/7868/1 "2020-05-27T10:35:57Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![dreamingmind](https://yyz1.discourse-cdn.com/flex029/user_avatar/discourse.cakephp.org/dreamingmind/32/1857_2.png) [@dreamingmind](https://discourse.cakephp.org/u/dreamingmind)\
**Post date:** [May 27, 2020, 3:05pm UTC](https://discourse.cakephp.org/t/filter-query-by-count-associated-data-cakephp-3/7868/2 "2020-05-27T15:05:21Z")

</div>

Not a direct answer but…

This might be a good time to consider the [CounterCache](https://book.cakephp.org/4/en/orm/behaviors/counter-cache.html) 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.

---

<div class="post-metadata">

**Author:** ![spooknick](https://yyz1.discourse-cdn.com/flex029/user_avatar/discourse.cakephp.org/spooknick/32/998_2.png) [@spooknick](https://discourse.cakephp.org/u/spooknick)\
**Post date:** [February 4, 2021, 11:52am UTC](https://discourse.cakephp.org/t/filter-query-by-count-associated-data-cakephp-3/7868/3 "2021-02-04T11:52:20Z")

</div>

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]);
```