Doctrine 2 pagination

1
votes

I use the following query:

SELECT u.username, u.password, s.name, s.price
FROM AcmeBundle:User u
LEFT JOIN AcmeBundle:Shop s
WITH u.id = s.id;

To display the records I want to paginate. The problem is that pagination required simple count query, and doesn't work with select like this one.

Not all identifier properties can be found in the ResultSetMapping: username

So to paginate it, I would have to always use two queries - the one above to display records, and the other one to count them, both with joins. It dramatically decreses the performance.

I use knplabs paginator, symfony 2 and doctrine ORM.

Can you give me any advices on how to do that?

2
phpmysqlsymfonydoctrine-ormpagination
See github.com/doctrine/doctrine2/issues/… – Gabriel

2 Answers

2
votes

It should be like 

  $dql = 'SELECT user
         FROM AcmeBundle:User user
         LEFT JOIN AcmeBundle:Shop shop
         WITH user.id = shop.id';
  $query = $em->createQuery($dql);
  $paginator  = $this->get('knp_paginator');
  $pagination = $paginator->paginate(
        $query,
        $this->get('request')->query->get('page', 1)/*page number*/,
        10/*limit per page*/
    );
  return array(
        'pagination' => $pagination,
    );

The whole object 'user' rather than attributes(user.username ..) should be passed in paginator ..

0
votes

https://github.com/doctrine/doctrine2/issues/2596#issuecomment-162359725 provides a reasonable solution, as @gabriel notes above. IOW:

SELECT PARTIAL u.{username, password, name, price} // ...