pagerfanta - DoctrineORMAdapter Sorting

Question

Although I know this is trivial I'm stuck trying to implement the pagerfanta Paginator using the DoctrineORMAdapter, I want to paginate all entities sorted by id in descending order, the final SQL I want is this:

SELECT id, name FROM User ORDER BY id DESC LIMIT 0, 5;

Suppose I had users from A to Z and I want to limit them by 5 each page, what DoctrineORMAdapter is paginating results in User E to A listed in the first page, while what I actually expect is to see User Z to user V in the first page, U to Q in the second page and so on. The DQL I'm passing to DoctrineORMAdapter is as follow:

SELECT u FROM My\FluffyBundle\Entity\User u ORDER BY u.id DESC

On execution this is the final DQL for the first page:

SELECT DISTINCT id0 FROM (SELECT u0_.id AS id0, u0_.name AS name1 FROM User u0_ 
ORDER BY u0_.id DESC) dctrn_result LIMIT 5 OFFSET 0

Please note that when using the ArrayAdapter instead of DoctrineORM's it works as expected, but it's not a good idea to rely on ArrayAdapter when you have thousands of complex Doctrine Entities, not even with extra lazy loading :D.

This is the only relevant code:

$queryBuilder = $repo->createQueryBuilder('u')->orderBy('u.id', 'DESC');
$adapter = new DoctrineORMAdapter($queryBuilder);
$pager = new Pagerfanta($adapter);
$pager->setMaxPerPage(5);

Thanks.

Answer 1

This will help you:

$adapter = new DoctrineORMAdapter($queryBuilder, false);

Had the same problem this morning. By default Pagerfanta is treating your query as one with joins. Setting second argument to false makes it use simple query handling.

In Kunstmaan Bundle, in AdminListConfiguration class, you have to overide function that is creating Pagerfanta, if you want to sort simple entity.