Lazy Loading and the N+1 Problem

What a proxy is, why a loop over associations fires one query per row, and how a join fetch removes them.

Syntax$qb->leftJoin('a.comments', 'c')->addSelect('c');

When Doctrine loads an entity, it does not load what the entity points at. In place of the related object it puts a proxy: an object of the right type that holds only the identifier and fetches the rest from the database the first time you read any other property. Collections work the same way, as a lazy collection that queries on first access. This is lazy loading, and it is the default.

The N+1 problem

Lazy loading is per object. Load twenty articles, then read each one's comments in a loop, and you get one query for the articles plus twenty for the comments. The Doctrine panel in the web profiler shows it plainly: twenty-one queries that are almost the same. The page still works, which is why this reaches production.

Fixing it with a join fetch

A fetch join loads both in one query. In a query builder, leftJoin('a.comments', 'c') alone only joins for filtering; addSelect('c') is what puts the comments in the result, so the collections arrive already populated and no further query fires. In DQL the same thing reads SELECT a, c FROM App\Entity\Article a LEFT JOIN a.comments c.

What not to reach for

Setting fetch: 'EAGER' on the mapping moves the problem rather than solving it: every query for that entity, everywhere, now drags the association along. fetch: 'EXTRA_LAZY' is narrower and genuinely useful, letting count(), contains() and slice() run as their own small queries instead of hydrating the whole collection. Caching an N+1 hides it until the cache misses.

Example

Example · php
<?php
// src/Repository/ArticleRepository.php

declare(strict_types=1);

namespace App\Repository;

use App\Entity\Article;
use Doctrine\Bundle\DoctrineBundle\Repository\ServiceEntityRepository;
use Doctrine\Persistence\ManagerRegistry;

/**
 * @extends ServiceEntityRepository<Article>
 */
class ArticleRepository extends ServiceEntityRepository
{
    public function __construct(ManagerRegistry $registry)
    {
        parent::__construct($registry, Article::class);
    }

    /**
     * One query for the articles and their comments together.
     *
     * @return list<Article>
     */
    public function findPublishedWithComments(\DateTimeImmutable $since): array
    {
        return $this->createQueryBuilder('a')
            // leftJoin on its own joins for filtering only.
            ->leftJoin('a.comments', 'c')
            // addSelect is what loads them, so the collection is not lazy.
            ->addSelect('c')
            ->andWhere('a.publishedAt >= :since')
            // Bound parameter, never string concatenation.
            ->setParameter('since', $since)
            ->orderBy('a.publishedAt', 'DESC')
            ->getQuery()
            ->getResult();
    }
}

When to use it

  • Turn a 40-query article list into a single query before shipping it.
  • Count a large comment collection without loading every row.
  • Load an aggregate and its children in one query for an edit screen.
  • Read the profiler's Doctrine panel to find where the duplicate queries come from.

More examples

The N+1, shown deliberately

GET /articles returns 200 with a JSON array like [{"title":"...","comments":4}]. The response is correct; the cost is not. With twenty articles the profiler's Doctrine panel reports twenty-one queries. Swapping findBy() for the repository method above drops it to one. Keep this shape in mind as a smell: a loop over entities that reads an association inside the loop body.

Example · php
<?php
// src/Controller/ArticleListController.php

declare(strict_types=1);

namespace App\Controller;

use App\Repository\ArticleRepository;
use Symfony\Bundle\FrameworkBundle\Controller\AbstractController;
use Symfony\Component\HttpFoundation\JsonResponse;
use Symfony\Component\Routing\Attribute\Route;

final class ArticleListController extends AbstractController
{
    // This controller demonstrates the N+1 problem. Do not copy the loop.
    #[Route('/articles', name: 'app_article_list', methods: ['GET'])]
    public function __invoke(ArticleRepository $articles): JsonResponse
    {
        $rows = [];

        // Query 1: the articles.
        foreach ($articles->findBy([], ['publishedAt' => 'DESC']) as $article) {
            $rows[] = [
                'title' => $article->getTitle(),
                // Queries 2..N+1: the lazy collection loads here, once
                // per article, because nothing fetched it in advance.
                'comments' => \count($article->getComments()),
            ];
        }

        return new JsonResponse($rows);
    }
}

The same fetch join in DQL

Naming both aliases in the SELECT is what makes it a fetch join: a and c are hydrated together, and the returned Article has its comments already in memory. The WHERE uses a bound parameter, so the slug can contain anything. getOneOrNullResult() returns a single Article or null even though the SQL returns one row per comment, because Doctrine collapses rows by identity. Reach for DQL over the query builder when the query is fixed and reads better as text.

Example · php
<?php
// src/Repository/ArticleRepository.php (extract)

declare(strict_types=1);

namespace App\Repository;

use App\Entity\Article;

// Inside ArticleRepository, which extends ServiceEntityRepository.
public function findBySlugWithComments(string $slug): ?Article
{
    $dql = <<<'DQL'
        SELECT a, c
        FROM App\Entity\Article a
        LEFT JOIN a.comments c
        WHERE a.slug = :slug
        ORDER BY c.createdAt ASC
        DQL;

    return $this->getEntityManager()
        ->createQuery($dql)
        ->setParameter('slug', $slug)
        ->getOneOrNullResult();
}

EXTRA_LAZY for a collection you only count

EXTRA_LAZY changes what a lazy collection does when you ask it something answerable in SQL. count(), containsKey(), isEmpty(), first(), get(), contains(), slice(), add() and offsetSet() avoid hydrating the whole collection. It is the right setting for an association with thousands of rows that a page only counts or paginates. It does not help a loop that reads the elements: that still needs a fetch join.

Example · php
<?php
// src/Entity/Article.php (extract)

declare(strict_types=1);

namespace App\Entity;

use Doctrine\Common\Collections\Collection;
use Doctrine\ORM\Mapping as ORM;

// Inside the Article entity.
/** @var Collection<int, Comment> */
#[ORM\OneToMany(
    targetEntity: Comment::class,
    mappedBy: 'article',
    fetch: 'EXTRA_LAZY',
)]
private Collection $comments;

// With EXTRA_LAZY, these run as small queries of their own instead of
// loading every Comment into memory first:
//   $article->getComments()->count()      -> SELECT COUNT(*) ...
//   $article->getComments()->contains($c) -> SELECT ... WHERE id = ?
//   $article->getComments()->slice(0, 10) -> SELECT ... LIMIT 10

Related

symfony querybuildersymfony dqlsymfony paginationsymfony doctrine performancesymfony relations basics

Discussion

  • Be the first to comment on this lesson.