How to identify and fix N+1 query problems in Doctrine

N+1 is a data-access design problem

A list can return the right content and still make far too many database calls. N+1 describes a common cause: one query loads the records, then processing each record triggers another query for related data. The shorthand is 1 + N, but the actual count depends on what is loaded and how it is used.

In Doctrine, this often means that the repository query and the code consuming its results disagree about the data needed. The extra queries may appear in a service, mapper, Twig template or serialiser. Lazy loading is useful; relying on it without checking the whole read path is where the cost becomes easy to miss.

Posts, authors and translations

Consider an illustrative list of published posts. Each post has one required author and a collection of translations. This entity excerpt shows only those associations:

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

class Post
{
    #[ORM\ManyToOne(targetEntity: User::class, fetch: 'LAZY')]
    #[ORM\JoinColumn(nullable: false)]
    private User $author;

    /** @var Collection<int, PostTranslation> */
    #[ORM\OneToMany(
        mappedBy: 'post',
        targetEntity: PostTranslation::class,
        fetch: 'LAZY',
    )]
    private Collection $translations;

    public function __construct(User $author)
    {
        $this->author = $author;
        $this->translations = new ArrayCollection();
    }
}

The entity attribute, identifier, other fields, accessors and the owning PostTranslation::$post mapping are omitted. New collections are initialised in the constructor. Post is not final so the example also suits configurations using generated subclass proxies; native lazy objects have different constraints.

Suppose translationFor($locale) searches the translations collection in memory, returning a translation or null. The following deliberately naïve read assumes every selected post has the requested translation:

$posts = $postRepository->findBy(
    ['published' => true],
    ['publishedAt' => 'DESC', 'id' => 'DESC'],
);

$result = [];

foreach ($posts as $post) {
    $result[] = [
        'title' => $post->translationFor($locale)->getTitle(),
        'author' => $post->getAuthor()->getDisplayName(),
    ];
}

With lazy associations, reading an unloaded author's display name can initialise that author. Searching an unloaded translations collection can load that post's translations. For the illustrative dataset below, assume a fresh EntityManager, a different author for every post, no cached results and one query for each association access:

Illustrative SQL counts, not measured results

Posts  List  Authors  Translations  Total
    5     1        5             5     11
   50     1       50            50    101
  500     1      500           500   1001

This is the often-quoted “about 101 queries for 50 posts” case, not a prediction for every application. Shared authors can be reused through Doctrine's identity map, which tracks managed objects by identity within an EntityManager. Previously loaded relations, mapping choices, fetch plans and result or second-level caches can change the count. Metadata and DQL query caches, by contrast, do not themselves cache the returned business data.

The loop also assumes a translation exists and loads all published posts. Those are deliberate simplifications for diagnosis, not a suitable list endpoint contract.

Confirm the repeated work

Start with a representative request in Symfony Profiler: inspect the Doctrine query count, cumulative database time and repeated SQL shapes. A fingerprint groups statements that have the same structure but different parameter values. Where available, a call stack helps connect the repeated statement to a relation getter.

Small development datasets, low database latency and already-managed fixture objects can conceal the problem. Compare, for example, 5, 50 and 500 records within the supported workload, using repeatable EntityManager and cache conditions. Look for per-item query growth, not an arbitrary requirement that every workload use a constant number of statements: batching may add queries at chunk boundaries.

Use the profiling or logging integration supported by the installed DBAL and DoctrineBundle versions. Middleware is the relevant mechanism in modern DBAL; old SQLLogger recipes are not portable to DBAL 4. A test collector should observe the measured operation, not fixture creation or unrelated framework bootstrap.

Trace the caller as well as the SQL. These accesses can perform database work when the relevant objects or collections are not loaded:

$post->getAuthor()->getDisplayName();
$post->getTranslations()->first();
count($post->getComments());

Returning an author proxy from getAuthor() does not necessarily initialise it; reading a non-identifier field generally does. With ordinary lazy collections, first() or count() can initialise the collection. getComments() is an additional illustrative association, omitted from the entity above. A mapper, debugger or logging formatter can trigger the same work if it reads these values.

Three ways to load what the list needs

Fetch join: return entities with selected relations loaded

This repository method keeps entity results while fetching the author and translations. The repository setup and imports are omitted:

/**
 * @return list<Post>
 */
public function findPublishedWithAuthorAndTranslations(): array
{
    return $this->createQueryBuilder('post')
        ->addSelect('author')
        ->addSelect('translation')
        ->innerJoin('post.author', 'author')
        ->leftJoin('post.translations', 'translation')
        ->andWhere('post.published = :published')
        ->setParameter('published', true)
        ->orderBy('post.publishedAt', 'DESC')
        ->addOrderBy('post.id', 'DESC')
        ->getQuery()
        ->getResult();
}

Here, selecting the joined aliases with addSelect() makes the joins fetch joins under object hydration. A join used only for filtering does not, by itself, populate the relation. The inner author join matches the required-author assumption; if authors are optional, the join and consumer must handle that. The left translation join keeps posts with no translations, so the consumer still needs a missing-translation policy.

The method intentionally has no page limit and loads every translation for matching posts. It is useful for a known, small result set or as a starting point for the paginated approach below—not an unbounded public listing. Sorting by publishedAt and then id gives posts a deterministic order. It does not define the order of translations within each collection.

Filtering this entity fetch join down to one locale would expose only that subset as the loaded collection. Code expecting all translations could then make the wrong assumption. For a locale-specific list, a projection often expresses the requirement more directly.

DTO projection: return the list's data contract

If the list needs four values, it need not receive an editable entity graph. This complete DTO declaration uses PHP 8.2+:

namespace App\ReadModel;

final readonly class PostListItem
{
    public function __construct(
        public int $id,
        public string $title,
        public string $authorName,
        public \DateTimeImmutable $publishedAt,
    ) {
    }
}

In the repository, import App\ReadModel\PostListItem. The DQL constructor arguments follow the DTO's parameter order:

/**
 * @return list<PostListItem>
 */
public function findPublishedList(
    string $locale,
    int $limit,
): array {
    if ($limit < 1) {
        throw new \InvalidArgumentException('limit >= 1');
    }

    return $this->createQueryBuilder('post')
        ->select(sprintf(
            'NEW %s(
                post.id,
                translation.title,
                author.displayName,
                post.publishedAt
            )',
            PostListItem::class,
        ))
        ->innerJoin('post.author', 'author')
        ->innerJoin(
            'post.translations',
            'translation',
            'WITH',
            'translation.locale = :locale',
        )
        ->andWhere('post.published = :published')
        ->setParameter('published', true)
        ->setParameter('locale', $locale)
        ->orderBy('post.publishedAt', 'DESC')
        ->addOrderBy('post.id', 'DESC')
        ->setMaxResults($limit)
        ->getQuery()
        ->getResult();
}

This example assumes an integer post.id, non-null titles and author names, and a publishedAt mapping of datetime_immutable with a value for every selected post. Different mappings require compatible constructor types; the DTO does not convert an arbitrary date string into a date object.

The locale condition selects one translation language. A database uniqueness constraint on the post/locale pair must ensure at most one matching translation. Under that assumption, and with one author per post, the query produces one DTO per matching post, so the limit applies to list items. Without it, duplicate translation rows can duplicate DTOs and shorten the page of unique posts.

The inner translation join deliberately excludes posts missing that locale. If the product requires a fallback language or a missing-title entry, design that explicitly: a left join alone would require a nullable title contract or a suitable fallback expression. The method returns the first limited set; subsequent pages need an offset or cursor and the same deterministic ordering.

Unlike entity hydration, this projection builds DTOs from selected field values. Their properties cannot lazy-load associations. Fewer columns and no managed entity graph may reduce memory and hydration work, but joins still have a cost: compare measurements. DTOs do not guarantee faster queries or replace entities needed for domain behaviour.

Batch loading: collect related data for the page

When collection joins would inflate the result, load a page first and retrieve the required related data for its IDs. Here, the project-specific findPublishedPage() must return an ordered list<Post> with authors already fetched through a to-one join. $page and $limit have been validated; getId() returns an integer for these persisted posts.

$posts = $postRepository->findPublishedPage($page, $limit);
$postIds = array_map(
    static fn (Post $post): int => $post->getId(),
    $posts,
);

$translations = $postIds === []
    ? []
    : $translationRepository->findIndexedByPostIds(
        postIds: $postIds,
        locale: $locale,
    );

$result = [];
foreach ($posts as $post) {
    $result[] = [
        'title' => $translations[$post->getId()] ?? null,
        'author' => $post->getAuthor()->getDisplayName(),
    ];
}

findIndexedByPostIds() is an illustrative repository method, not a Doctrine API. Its contract here is array<int, string>: post ID to title in the requested locale, loaded in one set-based query for the supplied IDs. It must apply the same access restrictions and translation uniqueness rule. The final loop reads this map, not translationFor(); a missing title is represented as null.

This example uses two data queries for a non-empty page under those assumptions. A total-count query, other related datasets or chunks imposed by parameter limits may add more. Fetching one relation query per ID inside the second method would simply move N+1 elsewhere.

Fetch settings, multiplied rows and pagination

LAZY defers association loading until needed. EAGER requests earlier loading, but does not mean “one SQL join”: Doctrine may use joins or additional queries depending on the mapping and loading path. Making every association eager can burden commands and screens that never need those relations.

For collections, EXTRA_LAZY allows supported operations such as count(), contains() and slice() without loading every element. It is not a promise that every collection operation avoids initialisation. Iteration still loads the collection; counting 50 unloaded collections can still issue 50 queries. Check the supported operations in the ORM version in use.

Joining several independent to-many collections creates a different risk. For one post with 4 translations, 20 comments and 8 tags, joining every combination can produce 4 × 20 × 8 = 640 SQL rows. Filters, join types and actual cardinalities affect that result. These are not 640 unique posts: normal object hydration coalesces root entities by identity, but database work, transferred rows and hydration still cost time and memory.

DISTINCT removes duplicate selected SQL rows where applicable. Rows containing different translation, comment or tag values are not identical, so it does not erase the cost of that multiplication.

A direct limit/offset on a to-many fetch join limits SQL rows, not complete posts and collections. A page may therefore contain fewer posts or incomplete collection data. Use Doctrine's pagination facilities appropriate to the installed version, with the collection-join setting and query walkers suited to the query. Their count and ID-selection queries also belong in the measurement.

An explicit alternative is to select, say, 25 unique post IDs in page order, then load details for those IDs. Both phases must use consistent filters and access boundaries. IN (:ids) does not preserve the order of its input: reapply the ordering or assemble results in ID-page order. If concurrent changes between the reads matter, define the required transaction isolation or snapshot behaviour.

To-one joins normally avoid collection multiplication, though other joins in the same query may still introduce it. Sorting posts by a to-many field needs a defined rule, such as the latest comment date, before choosing page IDs. Keyset pagination can help with deep sequential browsing, but does not naturally provide arbitrary page-number jumps or a total count. The product's navigation requirements matter.

Twig and serialisation are part of the read path

A template can reveal missing data loading without containing any explicit SQL:

{% for post in posts %}
    {{ post.author.displayName }}
    {{ post.translationFor(app.request.locale).title }}
{% endfor %}

Twig resolves these values through the object's accessors. The same loaded-relation and missing-translation assumptions apply as in the PHP loop. Prepare the required data before rendering, either as deliberately loaded entities or as a view model. A DTO is useful when it bounds what the view can read, not a requirement for every template.

JSON encoding needs a separate qualification:

json_encode($entity);

By default, json_encode() includes public properties; it does not universally traverse private Doctrine associations or call their getters. JsonSerializable::jsonSerialize() or other custom representation code may access relations. Symfony Serializer works through normalisers: an object normaliser may read getters or properties allowed by its configuration and serialisation groups. Check the actual normaliser and context, not just the controller.

A query-count test for an HTTP response should cover rendering or normalisation as well as repository loading. Measuring only the repository can miss the queries that happen afterwards.

Regression tests with a defined measurement window

These Pest examples are integration-test sketches. createPublishedPosts(), createPostFixtures(), entityManager(), doctrineQueryCollector(), service() and repository() are project-specific helpers. The collector's reset(), start(), stop() and queryCount() are its illustrative contract, not built-in Pest or Doctrine methods. It must count executed statements on the relevant connection. Middleware-based instrumentation must be configured before the connection is created.

The test harness must isolate datasets, configure caches predictably and resolve services without preloading the list. Flushing fixtures and clearing the same EntityManager used by the service avoids reusing fixture objects. Clearing it does not clear second-level or application caches.

Here, getPublishedPosts() is assumed to return a fully materialised list, not a deferred iterator. The budget of three queries is an example agreed for this service:

it('bounds the query count', function (int $count): void {
    $this->createPublishedPosts(count: $count, locale: 'en');

    $entityManager = $this->entityManager();
    $service = $this->service();
    $collector = $this->doctrineQueryCollector();
    $entityManager->flush();
    $entityManager->clear();

    $collector->reset();
    $collector->start();
    try {
        $result = $service->getPublishedPosts(
            locale: 'en',
            limit: $count,
        );
    } finally {
        $collector->stop();
    }

    expect($result)->toHaveCount($count)
        ->and($collector->queryCount())->toBeLessThanOrEqual(3);
})->with([5, 50]);

The dataset exercises both 5 and 50 posts; use distinct authors and several translations so relation reuse cannot hide the regression. Also test representative sharing patterns. For a deferred result, template or serialiser, consume it inside the measurement window. A query budget guards against repeated round trips; it is not a latency or memory benchmark.

The repository test below separates result construction from later property reads:

it('reads DTOs without further SQL', function (): void {
    $this->createPostFixtures(count: 20, locale: 'en');

    $entityManager = $this->entityManager();
    $repository = $this->repository();
    $collector = $this->doctrineQueryCollector();
    $entityManager->flush();
    $entityManager->clear();

    $items = $repository->findPublishedList('en', 20);

    expect($items)->toHaveCount(20)
        ->each->toBeInstanceOf(PostListItem::class);

    $collector->reset();
    $collector->start();
    try {
        foreach ($items as $item) {
            expect($item->title)->toBeString()
                ->and($item->authorName)->toBeString();
        }
    } finally {
        $collector->stop();
    }

    expect($collector->queryCount())->toBe(0);
});

It checks the number and type of DTOs, then verifies that reading their title and author fields executes no further SQL. It does not measure the cost of findPublishedList() itself. Add assertions with known fixture values for locale selection, missing translations, ordering, page boundaries and unique IDs; type checks alone do not prove those behaviours.

Type contracts and review of AI-generated changes

PHPStan can check that callers and implementations respect a declared return type. This declaration belongs inside an interface:

/**
 * @return list<PostListItem>
 */
public function findPublishedList(
    string $locale,
    int $limit,
): array;

Generic element types and nullable contracts make mistakes easier to detect. PHPStan does not execute SQL or detect N+1 directly, and arbitrary architecture rules require project-specific checks. Runtime measurements answer a different question from static analysis.

For a change proposed by a developer or an AI assistant, inspect relation access inside loops against the actual mapping and repository query. It is not automatically wrong when the data is already loaded. Reviewers should be able to explain which statement loads each required value, whether a mapper or serialiser adds work, and whether ordering and pagination still hold. Estimated query counts are a starting hypothesis; regression tests and measurements provide evidence. A local optimisation does not justify changing every fetch mapping.

Measure the release, not just the fix

In production, correlate endpoint latency percentiles with query count, total database time, repeated SQL fingerprints, page size and slow-query frequency. Include rows examined, hydration cost and memory where the monitoring tools can measure them. Many individually quick statements can be expensive together, so a slow-query log alone may miss N+1.

Use appropriately sampled traces and avoid retaining sensitive SQL parameters or customer content unnecessarily. Normalised SQL can still contain literals or identifying information; fingerprinting is not automatic anonymisation.

Caching can be worthwhile after the read path is understood. Check cold-cache behaviour, invalidation and keys scoped to the correct user or organisation. Moving the same loop into another service does not change its SQL; changing lazy-loading behaviour globally can break consumers without defining a better fetch plan.

A useful diagnosis ends with a comparison: reproduce the read, trace the repeated work, choose the smallest suitable loading change, and recheck content, order, locale and pagination. Record query count, database time and memory under comparable conditions, then watch the release. The right fix serves the read use case at an acceptable total cost—not merely with fewer statements.

Technical references

• Doctrine ORM: DQL, fetch joins and constructor expressions — https://www.doctrine-project.org/projects/doctrine-orm/en/3.6/reference/dql-doctrine-query-language.html
• Doctrine ORM: pagination — https://www.doctrine-project.org/projects/doctrine-orm/en/3.6/tutorials/pagination.html
• Doctrine ORM: extra-lazy associations — https://www.doctrine-project.org/projects/doctrine-orm/en/3.6/tutorials/extra-lazy-associations.html
• PHP: JSON encoding — https://www.php.net/manual/en/function.json-encode.php