Using pagination in Doctrine2/Symfony2 without Doctrine paginator extension

Question

I'm using Doctrine2 for a project that might get a lot of traffic and I'm welling to do some pagination in a search page and that only fetch 5 results per page So is there a good way for doing this without the need to use the doctrine extension and keeping the ORM abstraction layer? I mean I don't want to write any form of dql queries and keep my code in this format:

 $repo= $this->getDoctrine()
                    ->getEntityManager()
                    ->getRepository('AcmeOfficeBundle:Project');
        $list=$repo->findBy(array('PROJ_private' => "0"));

score 36 · Accepted Answer · edited Sep 20 '12 at 12:39

36

Doctrine 2.2 ships with a paginator. However, it does require you to write DQL queries.

If you insist on not writing any DQL, you can start by looking at the Doctrine EntityRepository class; specifically, the findBy() method. It has optional parameters for limit and offset, so you can try something like this (using your example as a baseline):

$num_pages = x; // some calculation of what page you're currently on
$repo = $this->getDoctrine()
                ->getRepository('AcmeOfficeBundle:Project');
$list = $repo->findBy(
    array('PROJ_private' => "0"), //search criteria, as usual
    array(/* orderBy criteria if needed, else empty array */),
    5, // limit
    5 * ($num_pages - 1) // offset
);

edited Sep 20 '12 at 12:39

Alex Pliutau

21,392
27
113
143

answered Mar 06 '12 at 16:30

Problematic

17,567
10
73
85

1

Thx for the part on Doctrine 2.2 shipping with a Paginator, didn't know that. – Matt Mar 06 '12 at 17:12
2

How would I get the total number of pages for a result set? Do I need to run a separate findBy() without the last two parameters to get the total number of results and then calculate it myself? That seems inefficient, but I'm not sure if there's a more elegant way to do it. – imkingdavid May 20 '14 at 15:52

Will B. · Answer 2 · 2015-07-29T20:41:57.333

In Doctrine ORM 2.3 you can also utilize Criteria along with matching on the entity repository. Which now (as of 2.5) works with nToMany relationships.

This helps when your query requires another comparison other than equals or when paginating a OneToMany collection of another entity.

$page = (isset($_GET['page']) && $_GET['page'] > 0 ? $_GET['page'] : 1);
$limit = 20;
$offset = ($limit * ($page - 1));
$criteria = \Doctrine\Common\Collections\Criteria::create()
    ->setMaxResults($limit)
    ->setFirstResult($offset);
$expr = $criteria->expr();
$user = $em->getRepository('AcmeOfficeBundle:Project')
    ->matching($criteria->where($expr->gt('PROJ_private', 0)));
$total_records = $user->count();

http://doctrine-orm.readthedocs.org/en/latest/reference/working-with-associations.html#filtering-collections

score 0 · Answer 3 · answered May 15 '14 at 12:48

A good option that avoids writing DQL is to operate on collections using Pagerfanta

https://github.com/whiteoctober/Pagerfanta

use Pagerfanta\Adapter\DoctrineCollectionAdapter;
$user = $em->find("App\DoctrineORM\User", 1);
$adapter = new DoctrineCollectionAdapter($user->getGroups());

Using pagination in Doctrine2/Symfony2 without Doctrine paginator extension

3 Answers3