Doctrine Performance: Solving the N+1 Problem with DQL and QueryBuilder

1. Understanding the N+1 problem: how it arises

The N+1 problem in Doctrine ORM arises from lazy loading, the default behavior for all associations. When you load a list of products and then access the category for each product, Doctrine issues a separate SELECT query for every single category. For 50 products that means 51 queries: 1 for the product list and 50 for the categories. For 500 products it is 501 queries. The N+1 problem scales linearly with the amount of data, and it often stays invisible during development with a small amount of test data, only becoming a problem in production with real data volumes.

The typical code that creates the N+1 problem looks harmless. A Twig template iterates over a product list and accesses product.category.name. The actual query behind that access is invisible to the developer, Doctrine's lazy loading makes the database access transparent. That is exactly the problem: the developer sees no database query in the template code, Doctrine hides it, but the database server takes the full hit.

Another common scenario for the N+1 problem: orders with order items. An order list loads all orders, then the template iterates over order.items for every order, one separate query against the order_item table per order. With 100 orders averaging 5 items each, that is 100 separate SQL queries, even though a single JOIN could have delivered all the data. This is the N+1 problem in its most common form.

2. Detecting N+1 problems in the Symfony Profiler

The Symfony Profiler is the most important tool for detecting the N+1 problem. In the Doctrine tab of the profiler, the number of executed database queries immediately reveals whether there is an issue. A single listing with 20 items should never generate more than 5 to 10 queries, if you see 25 or 200 there, the N+1 problem is active. The profiler also shows the exact SQL statements, the stack traces and the execution times, which makes it possible to pinpoint the exact location of the problem.

For production, where the profiler is not available, the Doctrine DBAL logging configuration helps: every query above a threshold gets logged. The symfony/debug-bundle bundle can also flag slow queries in development mode. Another tool is the SQLLogger interface from doctrine/orm for custom logging. Detecting the N+1 problem in production is harder than in development, which is why writing tests that verify the number of SQL queries is an important practice.

A concrete testing approach: use $this->getQueryCount() in Symfony tests to measure the number of queries before and after the code section under test. If the count rises proportionally to the amount of data, the N+1 problem is present. If the count stays constant (or only grows logarithmically), the problem has been fixed. Doctrine's SQLLogger decorator offers a simple way to count queries within a test.

<?php

declare(strict_types=1);

namespace App\Repository;

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

/**
 * ProductRepository demonstrating N+1 problem and its solution.
 */
class ProductRepository extends ServiceEntityRepository
{
    public function __construct(ManagerRegistry $registry)
    {
        parent::__construct($registry, Product::class);
    }

    /**
     * BAD: Returns products with LAZY-loaded categories.
     * Accessing product->category in a loop triggers N+1: 1 + N queries.
     *
     * @return Product[]
     */
    public function findAllLazy(): array
    {
        // Only 1 query here, but N additional queries when accessing categories
        return $this->findAll();
    }

    /**
     * GOOD: Returns products with eagerly-loaded categories via JOIN FETCH.
     * Single query, no additional queries when accessing product->category.
     *
     * @return Product[]
     */
    public function findAllWithCategory(): array
    {
        return $this->createQueryBuilder('p')
            ->addSelect('c')               // FETCH join, loads category data into objects
            ->leftJoin('p.category', 'c')  // Regular join without addSelect would NOT fix N+1
            ->getQuery()
            ->getResult();
    }
}

// Usage comparison:
// findAllLazy() + template accessing category.name = 1 + N queries (N+1 problem)
// findAllWithCategory() + template accessing category.name = 1 query (fixed)

3. JOIN FETCH in DQL: the most direct solution

The most direct solution for the N+1 problem in Doctrine ORM is the JOIN FETCH command in DQL (Doctrine Query Language). Unlike a regular join, JOIN FETCH loads the associated objects directly into the EntityManager's identity map cache, so no further database access is needed once the association is accessed later. The result is fully hydrated objects after a single SQL query.

In DQL the syntax reads: SELECT p, c FROM App\Entity\Product p JOIN FETCH p.category c. Important: JOIN FETCH in DQL is directly usable only for ManyToOne and OneToOne associations. For OneToMany and ManyToMany associations (for example product-to-tags) you have to use addSelect('t') together with leftJoin('p.tags', 't') in the QueryBuilder, the DQL equivalent being SELECT p, t FROM Product p LEFT JOIN FETCH p.tags t. Without the FETCH (or, in the QueryBuilder, without addSelect), it is a regular join that does not solve the N+1 problem.

A common misunderstanding about the N+1 problem: a regular JOIN without FETCH does not solve it. Doctrine uses the JOIN for the WHERE clause (filtering by associated data), but still lazy-loads the associated objects, the signal to hydrate the association data into the resulting PHP objects is missing. Only by explicitly selecting the association in the SELECT part of the query, either via SELECT p, c in DQL or addSelect('c') in the QueryBuilder, is lazy loading disabled.

4. QueryBuilder with addSelect and leftJoin

The Doctrine QueryBuilder is the most widely used tool for dynamic query construction in Symfony projects. For the N+1 problem, the interplay between leftJoin() and addSelect() is decisive. leftJoin('p.category', 'c') generates the SQL JOIN, but without the accompanying addSelect('c'), the category data is not loaded into the PHP objects. Only when both calls are used together are the associations fully populated in the resulting array of products, no further database access, no N+1 problem.

The QueryBuilder is especially useful when the associations that need to be loaded are dynamic, for example depending on query parameters or user permissions. You can call addSelect() and leftJoin() conditionally inside a method and extend the QueryBuilder step by step. That is one of the key advantages of the QueryBuilder over plain DQL: the query is assembled programmatically, without string manipulation.

<?php

declare(strict_types=1);

namespace App\Repository;

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

/**
 * OrderRepository with QueryBuilder solutions for N+1 problem.
 * Demonstrates eager loading of multiple associations in one query.
 */
class OrderRepository extends ServiceEntityRepository
{
    public function __construct(ManagerRegistry $registry)
    {
        parent::__construct($registry, Order::class);
    }

    /**
     * Load orders with all items and item products in a single query.
     * Without addSelect for items and products → N+1 problem for each association.
     *
     * @return Order[]
     */
    public function findRecentOrdersWithDetails(int $limit = 50): array
    {
        return $this->createQueryBuilder('o')
            ->addSelect('i')               // Eager-load order items, fixes N+1 for items
            ->addSelect('p')               // Eager-load item products, fixes N+1 for products
            ->addSelect('c')               // Eager-load customer, fixes N+1 for customer
            ->leftJoin('o.items', 'i')
            ->leftJoin('i.product', 'p')
            ->leftJoin('o.customer', 'c')
            ->where('o.createdAt >= :since')
            ->setParameter('since', new \DateTimeImmutable('-30 days'))
            ->orderBy('o.createdAt', 'DESC')
            ->setMaxResults($limit)
            ->getQuery()
            ->getResult();
    }

    /**
     * DQL equivalent, identical result, explicit syntax.
     * SELECT o, i, p, c FROM App\Entity\Order o
     * LEFT JOIN FETCH o.items i
     * LEFT JOIN FETCH i.product p
     * LEFT JOIN FETCH o.customer c
     * WHERE o.createdAt >= :since
     * ORDER BY o.createdAt DESC
     *
     * @return Order[]
     */
    public function findRecentOrdersWithDetailsDQL(int $limit = 50): array
    {
        return $this->getEntityManager()
            ->createQuery('
                SELECT o, i, p, c
                FROM App\Entity\Order o
                LEFT JOIN o.items i
                LEFT JOIN i.product p
                LEFT JOIN o.customer c
                WHERE o.createdAt >= :since
                ORDER BY o.createdAt DESC
            ')
            ->setParameter('since', new \DateTimeImmutable('-30 days'))
            ->setMaxResults($limit)
            ->getResult();
    }
}

5. Fetch join vs. regular join: the difference

The difference between a fetch join and a regular join in Doctrine ORM is one of the most commonly misunderstood aspects of solving the N+1 problem. A regular join, leftJoin('p.category', 'c') without addSelect('c'), generates a SQL JOIN and allows filtering or sorting by associated fields. The category data, however, is not hydrated into the PHP objects, because Doctrine does not know that it is wanted.

A fetch join, leftJoin() with addSelect() or JOIN FETCH in DQL, explicitly instructs Doctrine to hydrate the associated data into the resulting PHP objects. Doctrine places the category objects into the identity map cache and connects them to the product objects, so accessing product.category does not trigger any further database access. This is the only mechanism that fully solves the N+1 problem for associations in Doctrine.

6. Pagination and JOIN FETCH: the limit problem

Combining pagination (setMaxResults()) with fetch joins for OneToMany associations creates a well-known problem in Doctrine: the limit is applied at the SQL level, where the JOIN produces multiple rows per entity. An order with 5 items results in 5 SQL rows, so a LIMIT 10 then does not return 10 orders but 2 orders with 5 items each. Doctrine recognizes this problem and raises a warning: Use Paginator to count the total number of elements in the result set...

The solution for the N+1 problem with pagination is Doctrine's Paginator class. It solves the problem with two queries: first a subquery that fetches the correct IDs with the LIMIT, then a second query with those IDs and the fetch join. For 10 items that yields exactly 2 queries instead of 11, a small trade-off for correct pagination. The alternative is to avoid fetch joins for OneToMany associations entirely and instead load the IDs in one query and load the associations in a second batch query.

7. Batching and iterators for large datasets

For large datasets, exports, migrations, batch processing, avoiding the N+1 problem alone is not enough. Loading 10,000 entities at once puts significant pressure on PHP memory, because Doctrine keeps every object in the identity map. The solution: iterators and explicit identity map clearing in batches. With $query->toIterable() (Doctrine ORM 2.8+), entities are processed in chunks without loading them all into memory at once.

The batch processing pattern for Doctrine: define a chunk size (for example 500 entities), call $entityManager->clear() after each chunk to empty the identity map, and if the code performs writes, call flush() and clear() after every chunk. This pattern drastically reduces peak memory usage while at the same time avoiding the N+1 problem, because the query for each chunk loads every required association with JOIN FETCH. Combining query iteration with explicit EntityManager management is the only way to process large Doctrine datasets in a memory-efficient way.

<?php

declare(strict_types=1);

namespace App\Service;

use App\Entity\Product;
use App\Repository\ProductRepository;
use Doctrine\ORM\EntityManagerInterface;

/**
 * Batch processing service, avoids N+1 problem and memory exhaustion.
 * Processes large datasets in chunks with explicit Identity Map clearing.
 */
final class ProductBatchProcessor
{
    private const BATCH_SIZE = 500;

    public function __construct(
        private readonly ProductRepository $productRepository,
        private readonly EntityManagerInterface $entityManager,
        private readonly SearchIndexService $searchIndex,
    ) {}

    /**
     * Re-index all products in batches, memory-efficient for large datasets.
     * JOIN FETCH for category eliminates N+1 per batch.
     */
    public function reindexAllProducts(): void
    {
        $offset = 0;
        $processed = 0;

        do {
            // Fetch batch with category eager-loaded, no N+1 within batch
            $products = $this->productRepository->createQueryBuilder('p')
                ->addSelect('c')
                ->leftJoin('p.category', 'c')
                ->setFirstResult($offset)
                ->setMaxResults(self::BATCH_SIZE)
                ->getQuery()
                ->getResult();

            if (empty($products)) {
                break;
            }

            foreach ($products as $product) {
                // category is already loaded, no additional query here
                $this->searchIndex->indexProduct($product);
                ++$processed;
            }

            // Flush and clear, frees memory, resets Identity Map
            $this->entityManager->flush();
            $this->entityManager->clear();

            $offset += self::BATCH_SIZE;

            // Products are detached after clear(), do not use them after this point
        } while (count($products) === self::BATCH_SIZE);

        // Final flush for any remaining items
        $this->entityManager->flush();
    }
}

8. Partial objects and EXTRA_LAZY associations

When not all fields of an entity need to be loaded, partial objects offer another performance lever: with SELECT PARTIAL p.{id, name, price} in DQL, Doctrine loads only the specified fields, not the full entity. This reduces the amount of data transferred from the database and the hydration time, especially for entities with many fields or large text/JSON fields. The trade-off: partial objects must not be persisted, because not all fields are known. They are pure read objects.

Another strategy is the EXTRA_LAZY loading strategy for associations. With fetch: 'EXTRA_LAZY' in the association configuration, Doctrine enables optimized COUNT and SLICE operations on collections without loading the entire collection. $product->getTags()->count() generates a SELECT COUNT(*) under EXTRA_LAZY instead of loading all tags. This is ideal for use cases where you only want to display the number of associated items, not the items themselves, and it solves a specific sub-aspect of the N+1 problem without a full JOIN fetch solution.

9. Comparison: query strategies and their performance impact

In a typical Symfony project with a product list of 100 entries and categories, benchmarking shows: lazy loading generates 101 SQL queries and takes 380ms. JOIN FETCH via the QueryBuilder generates 1 SQL query and takes 45ms, a reduction of 88% in database time. The total page load time drops from 620ms to 120ms. Solving the N+1 problem is, in this scenario, the single biggest performance improvement achievable without caching infrastructure.

Important when evaluating this: JOIN FETCH loads more data per query and increases PHP memory usage, because every association is fully hydrated. For listings with 20 to 50 items that is irrelevant, for bulk exports of 50,000 entities batch processing is the better strategy. The right solution for the N+1 problem always depends on the use case: lists use JOIN FETCH, bulk operations use batch processing with identity map clearing, count operations use EXTRA_LAZY.

10. Summary

The N+1 problem in Doctrine ORM arises from lazy loading of associations inside loops. For lists and overviews, JOIN FETCH via DQL or addSelect() plus leftJoin() in the QueryBuilder is the most direct solution: one SQL query instead of N+1. For pagination with OneToMany associations, Doctrine's Paginator class correctly solves the problem with two queries. For bulk exports and batch processing, the chunk pattern with entityManager->clear() eliminates both the N+1 problem and the memory overhead.

The Symfony Profiler is the central tool for detection, any listing page with more than 10 SQL queries deserves a review. Writing query count tests prevents the N+1 problem from being reintroduced by later refactorings or new template accesses. Eliminating the N+1 problem is, in most Symfony projects, the most effective single performance measure achievable without caching infrastructure.

11. FAQ: Doctrine N+1 problem and performance