Symfony Published on

Filtering without truncating collections

A WHERE on a join loaded with addSelect() leaves collections incomplete, and they stay that way in the EntityManager. EXISTS filters without cutting anything.

Topics covered: DoctrineRelationsQueryBuilderN+1 queries

(The classes keep their French names, Commande for the order and LigneDeCommande for an order item.)

At the café, some order items are free. This morning, Ada had two coffees, a tea and a free croissant: her invoice comes to €8.00. The owner wants the list of orders that have at least one free item, with their items, in a single query to avoid N+1. CommandeRepository::withAFreeItem() does this:

$this->createQueryBuilder('o')
    ->join('o.items', 'i')
    ->addSelect('i')
    ->where('i.free = true')
    ->orderBy('o.id')
    ->getQuery()
    ->getResult();

The list does hold the right orders, Ada's and Alan's. But look at Ada's items in the preview (totals are in cents):

{ "customer": "Ada", "items": ["Croissant"], "total": 0 }

Only the croissant is left. The WHERE did two jobs at once. It picked the orders, which is what we wanted. But it also filtered the items the join returns, and with addSelect('i'), those are the items Doctrine puts into getItems(). It then treats the collection as complete and won't read it again.

The bug doesn't stop at the list. Ada's order stays in the EntityManager. When the controller then issues her invoice with findOneBy(), Doctrine returns the same object, with the same truncated collection: a €0 invoice instead of €8. In a console command or a Messenger worker, where the EntityManager lives a long time, this kind of error shows up far from its cause.

So the two jobs have to be split: a condition that picks the orders without touching the join, and a join that fills the collections without any filter. In DQL, a condition such as EXISTS (subquery) can do the first job.

Your task

The /orders/with-free-item preview lists today's orders that have a free item, then issues Ada's invoice with findOneBy().

  1. Fix CommandeRepository::withAFreeItem() so that each order keeps all its items.
  2. Keep a single SQL query for the list and its items.

The controller and the entities don't change.