Custom pagination count query in CakePHP 3

Need Help
1

Hi there! I’m looking for a way to have a custom pagination query for the count, like was possible in Cake2 with paginateCount().

I have a performance problem on an articles table joined with categories. The paginate query takes 20ms, while the count query takes an average of 500ms. If I manually run the count query on DB without the join, it takes 30ms.

Now, I know that executing the same exact query leaves no space for mistakes and for incorrect numbering, but in this case I don’t really need the ->contain([‘Categories’]) on the count query since it’s not changing the number of returned rows, and I’m looking for a way to remove it. It seems that with the new ORM is not possible to use a custom count query and cannot find a solution.

Any ideas? Thanks!

2

Did you manage to solve issue of pagination using custom query?
I am facing issue with multiple joins in query.
Any help appreciated.

3

You can use Query::counter, It must return an int.
I used because I needed the count for two models at the same time (something like total parent count and children count).

$query->counter(function (Query $query) {
  return $query->
      ->select([
         'count' => $query->func()->count('id'),
      ], true)
      ->group([], true)
      ->disableHydration()
      ->first()['count'];
});