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), aftersetFirstResult()andsetMaxResults(), without callinggetResult(); - 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.
- Fix
CommandeRepository::page()so that each page shows two complete orders. - Keep loading the items in the query: two SQL queries at most per page.
The controller and the entities don't change.