Doctrine ORM: Detecting and Solving the N+1 Problem

Anyone who has worked with Doctrine ORM for a while has stumbled upon the N+1 problem — often without realizing it. The application runs smoothly locally, but in production with real data volumes it suddenly becomes slow. The profiler shows 300 database queries for a single page. That is the N+1 problem.

How the N+1 Problem Occurs¶

Consider a simple blog application: posts with associated authors. The entities:

<?php

declare(strict_types=1);

namespace App\Entity;

use Doctrine\ORM\Mapping as ORM;

#[ORM\Entity]
class Post
{
    #[ORM\Id]
    #[ORM\GeneratedValue]
    #[ORM\Column]
    private int $id;

    #[ORM\Column(length: 255)]
    private string $title;

    #[ORM\ManyToOne(targetEntity: Author::class)]
    private Author $author;

    public function getId(): int { return $this->id; }
    public function getTitle(): string { return $this->title; }
    public function getAuthor(): Author { return $this->author; }
}

<?php

declare(strict_types=1);

namespace App\Entity;

use Doctrine\ORM\Mapping as ORM;

#[ORM\Entity]
class Author
{
    #[ORM\Id]
    #[ORM\GeneratedValue]
    #[ORM\Column]
    private int $id;

    #[ORM\Column(length: 100)]
    private string $name;

    public function getId(): int { return $this->id; }
    public function getName(): string { return $this->name; }
}

In the controller you load all posts:

$posts = $this->postRepository->findAll();

In the template you iterate over the posts and access the author:

{% for post in posts %}
    <p>{{ post.title }} von {{ post.author.name }}</p>
{% endfor %}

The problem: Doctrine first loads all posts with a single query (SELECT * FROM post). When you then call post.author.name, Doctrine loads the author via lazy loading — a separate query for each post. With 100 posts, this produces 101 queries: 1 for the posts, 100 for the authors. That is the N+1 problem.

Detecting the Problem¶

Symfony Profiler¶

In development mode, the Symfony Profiler shows all queries under "Doctrine". A warning sign is when similar queries repeat — only with different IDs:

SELECT * FROM author WHERE id = 1
SELECT * FROM author WHERE id = 2
SELECT * FROM author WHERE id = 3
-- ... 97 more

Doctrine Extensions: Logging Middleware¶

For automated tests you can use Doctrine\DBAL\Logging\Middleware to count queries. The old SQLLogger interface has been deprecated since DBAL 3.2 and removed in DBAL 4:

<?php

declare(strict_types=1);

namespace App\Tests;

use Doctrine\DBAL\Driver\Middleware\AbstractDriverMiddleware;
use Doctrine\DBAL\Driver as DriverInterface;
use Doctrine\DBAL\Driver\Connection as ConnectionInterface;
use Doctrine\ORM\EntityManagerInterface;
use Symfony\Bundle\FrameworkBundle\Test\KernelTestCase;

class PostRepositoryTest extends KernelTestCase
{
    public function testNoNPlusOneOnPostList(): void
    {
        $entityManager = static::getContainer()->get(EntityManagerInterface::class);
        $connection = $entityManager->getConnection();

        // Enable debug mode to collect queries via the DebugStack
        $configuration = $connection->getConfiguration();
        $queryCount = 0;

        $logger = new class ($queryCount) extends \Psr\Log\AbstractLogger {
            public function __construct(private int &$count) {}
            public function log($level, string|\Stringable $message, array $context = []): void
            {
                $this->count++;
            }
        };

        $loggingMiddleware = new \Doctrine\DBAL\Logging\Middleware($logger);
        $configuration->setMiddlewares([$loggingMiddleware]);

        $posts = static::getContainer()->get(PostRepository::class)->findAllWithAuthor();

        $this->assertLessThanOrEqual(2, $queryCount, 'More than 2 queries — N+1 problem!');
    }
}

Solution 1: JOIN FETCH in DQL¶

The cleanest solution is an explicit JOIN in the repository:

<?php

declare(strict_types=1);

namespace App\Repository;

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

class PostRepository extends ServiceEntityRepository
{
    public function __construct(ManagerRegistry $registry)
    {
        parent::__construct($registry, Post::class);
    }

    /**
     * @return Post[]
     */
    public function findAllWithAuthor(): array
    {
        return $this->createQueryBuilder('p')
            ->addSelect('a')
            ->join('p.author', 'a')
            ->orderBy('p.id', 'DESC')
            ->getQuery()
            ->getResult();
    }
}

The addSelect('a') tells Doctrine to load the author at the same time. This produces a single query with JOIN:

SELECT p.*, a.* FROM post p INNER JOIN author a ON p.author_id = a.id ORDER BY p.id DESC

Solution 2: fetch="EAGER" on the Entity (Not Recommended)¶

You can also set fetch: 'EAGER' at the entity level:

#[ORM\ManyToOne(targetEntity: Author::class, fetch: 'EAGER')]
private Author $author;

This causes Doctrine to eagerly load the relation — even when you do not need the author. Depending on the association type, Doctrine will use either a JOIN or a separate query. For ManyToOne and OneToOne, a JOIN is typically used, while OneToMany and ManyToMany result in separate queries. This can make other queries unnecessarily slower. The explicit solution in the repository is more flexible.

Solution 3: EXTRA_LAZY for Large Collections¶

When a collection has many elements and you do not want to load them all at once, use EXTRA_LAZY. This avoids full collection hydration for operations like count(), contains(), or slice():

#[ORM\OneToMany(targetEntity: Comment::class, mappedBy: 'post', fetch: 'EXTRA_LAZY')]
private Collection $comments;

EXTRA_LAZY prevents the entire collection from being loaded into memory when calling count(), contains(), or slice(). Instead, Doctrine executes optimized SQL queries for these operations (e.g. SELECT COUNT(*)). When fully accessing the collection (e.g. iteration), it is loaded completely as usual.

Solution 4: Native Query for Complex Cases¶

For very complex queries, use a native query with ResultSetMapping:

public function findRecentPostsWithStats(): array
{
    $rsm = new \Doctrine\ORM\Query\ResultSetMappingBuilder($this->getEntityManager());
    $rsm->addRootEntityFromClassMetadata(Post::class, 'p');
    $rsm->addJoinedEntityFromClassMetadata(Author::class, 'a', 'p', 'author');

    $sql = '
        SELECT p.*, a.*
        FROM post p
        INNER JOIN author a ON p.author_id = a.id
        WHERE p.created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
        ORDER BY p.id DESC
        LIMIT 20
    ';

    return $this->getEntityManager()
        ->createNativeQuery($sql, $rsm)
        ->getResult();
}

N+1 with One-to-Many Relations¶

The problem also occurs with one-to-many relations, but is more complicated to solve. You have authors with many posts:

// N+1: For each author, the post collection is loaded individually
foreach ($authors as $author) {
    echo $author->getName() . ': ' . $author->getPosts()->count() . ' Posts';
}

Solution: Write a custom query with GROUP BY:

public function findAuthorsWithPostCount(): array
{
    return $this->createQueryBuilder('a')
        ->addSelect('COUNT(p.id) as postCount')
        ->leftJoin('a.posts', 'p')
        ->groupBy('a.id')
        ->orderBy('postCount', 'DESC')
        ->getQuery()
        ->getResult();
}

Checklist Against N+1¶

• Write repository methods with JOIN FETCH instead of using findAll()
• Regularly check queries in the Symfony Profiler (dev mode)
• Automated tests with query counter for critical endpoints
• Avoid fetch: 'EAGER' at the entity level — too global
• With pagination: include LIMIT and OFFSET in the query to avoid loading unnecessary objects

The N+1 problem can always be solved. The art lies in detecting it early — preferably before the production database has 100,000 records.