Problem to paginate query with aggregates in sub queries

[maximecolin]

I have to paginate a Doctrine query with sub queries doing aggregates. I have to paginate over 1000 rows.

My orignal query looks like :

SELECT 
   r, u, d, c.value AS value_c, 
   (SELECT COUNT(a.id) FROM Model:EntityA a WHERE ...) AS count_a, 
   (SELECT SUM(b.number) FROM Model:EntityB b) AS sum_b, ...
FROM Model:EntityR r
LEFT JOIN Model:EntityC c,
LEFT JOIN Model:EntityR r,
LEFT JOIN Model:EntityU u
WHERE ...

I have 5 aggregates like this.

1. Paginator do a select count (original query)
2. Paginator do a select distinct id ... with limit/offset
3. Paginator run original with a where in clause

1 is very slow because it run the query and aggregates on all 1000 rows. I solved it by using the knp_paginator.count hint to do my own count query.

2 souldn't be slow because it select only id from the original query. Nevertheless, it's not the case. All the fields of the selected entities are removed from the original query but the aliased field and subquery are kept. So paginator run a query like :

SELECT DISTINCT r.id, c.value AS value_c (SELECT COUNT(a.id) FROM ...) AS count_a, (SELECT SUM(b.number) FROM ...)  FROM ...

Instead of :

SELECT DISTINCT r.id FROM ...

It as for consequence to run aggregates sub query on all the 1000 rows which is very slow.

The problem seems to be in the Knp\Component\Pager\Event\Subscriber\Paginate\Doctrine\ORM\QuerySubscriber where custom tree walkers are added.

Why my subquery aren't removed from the original query ? Is it a way to inject a custom query result as the knp_paginator.count hint ?

[webdevilopers]

Have you found any solution or did you try PagerFanta @maximecolin ?

[lunetics]

Same here. It seems The Doctrine Paginator issues the method getCountQuery, even if the "knp_pager.count" hint is set.

That results for me in an error (Single id is not allowed on composite primary key"

can you check the value of Doctrine/ORM/Tools/Pagination/Paginator count() method (is it null) ?