Doctrine ORM — relations, N+1 and query optimization

Types of relations in Doctrine

// ManyToOne — many articles belong to one user
#[ORM\ManyToOne(targetEntity: User::class)]
#[ORM\JoinColumn(nullable: false)]
private ?User $author = null;

// OneToMany — one article has many comments
#[ORM\OneToMany(targetEntity: Comment::class, mappedBy: 'article', cascade: ['remove'])]
private Collection $comments;

// ManyToMany — articles and tags
#[ORM\ManyToMany(targetEntity: Tag::class, inversedBy: 'articles')]
#[ORM\JoinTable(name: 'article_tags')]
private Collection $tags;

The N+1 problem

It's the most common performance mistake in applications with an ORM:

// BAD — 1 + N queries (1 for the list + one for each author)
$articles = $repo->findAll();
foreach ($articles as $article) {
    echo $article->getAuthor()->getName(); // ← separate SQL!
}

With 100 articles = 101 database queries.

Solution: JOIN FETCH

// GOOD — 1 query with a JOIN
public function findWithAuthors(): array
{
    return $this->createQueryBuilder('a')
        ->leftJoin('a.author', 'u')
        ->addSelect('u')                        // ← this is crucial!
        ->leftJoin('a.tags', 't')
        ->addSelect('t')
        ->where('a.status = :status')
        ->setParameter('status', 'published')
        ->orderBy('a.publishedAt', 'DESC')
        ->getQuery()
        ->getResult();
}

Pagination with Pagerfanta

Don't load everything — limit the results:

use Pagerfanta\Doctrine\ORM\QueryAdapter;
use Pagerfanta\Pagerfanta;

public function findPaginated(int $page, int $perPage = 10): Pagerfanta
{
    $qb = $this->createQueryBuilder('a')
        ->where('a.status = :status')
        ->setParameter('status', 'published')
        ->orderBy('a.publishedAt', 'DESC');

    $pagerfanta = new Pagerfanta(new QueryAdapter($qb));
    $pagerfanta->setMaxPerPage($perPage);
    $pagerfanta->setCurrentPage($page);

    return $pagerfanta;
}

Batch processing — importing large datasets

public function importArticles(array $data): void
{
    foreach ($data as $i => $row) {
        $article = new BlogArticle();
        $article->setTitle($row['title']);
        // ...
        $this->em->persist($article);

        // every 100 records — flush and clear the Unit of Work
        if ($i % 100 === 0) {
            $this->em->flush();
            $this->em->clear();  // ← without this memory explodes
        }
    }
    $this->em->flush(); // the rest
}

Diagnostics — SQL Logger

In the development environment, enable query logging to the Symfony profiler. Look for:

• queries > 100ms
• repeated queries (a sign of N+1)
• SELECT * instead of specific columns

# how many queries ran for a given request?
# Open the Symfony Profiler → "Doctrine" tab

Cheat sheet — fetch mode