Symfony Published on

Paginating orders loaded with their items

On a query with a join, setMaxResults() limits SQL rows, not entities: Doctrine's Paginator pages through orders without cutting their collections.

Topics covered: DoctrineRelationsPaginationN+1 queries

The café's history shows orders two at a time, each with its items. (The classes keep their French names, Commande for the order and LigneDeCommande for an order item, as in the French version of the exercise.) To avoid one query per order, CommandeRepository::page() loads the items in the same query, with a join and addSelect(), then bounds it:

return $this->createQueryBuilder('o')
    ->leftJoin('o.items', 'i')
    ->addSelect('i')
    ->orderBy('o.id')
    ->setFirstResult(($page - 1) * self::PER_PAGE)
    ->setMaxResults(self::PER_PAGE)
    ->getQuery()
    ->getResult();

The result is wrong, without any error. With a join, the database returns one SQL row per order item. Ada's order has three: LIMIT 2 keeps two of them, so a single order, with two items out of three. And the OFFSET of the following pages shifts the same way. Since orders don't all have the same number of items, no fixed limit gets it right.

Doctrine has the tool for this: its Paginator. It works in two steps: one query looks up the ids of the orders on the page, a second one loads those orders with all their items.

Three things to know to use it:

  • it comes from Doctrine\ORM\Tools\Pagination\Paginator;
  • it is built from the query once it is bounded: new Paginator($queryBuilder), after setFirstResult() and setMaxResults(), without calling getResult();
  • you loop over it like an array, with foreach.

Your task

The /orders?page=1 preview shows the first page of the history: five orders of one to four items, two per page.

  1. Fix CommandeRepository::page() so that each page shows two complete orders.
  2. Keep loading the items in the query: two SQL queries at most per page.

The controller and the entities don't change.