If you've ever opened your profiler and seen dozens or hundreds of nearly identical SQL queries, you've probably met the infamous N+1 problem.

It's sneaky. Your code looks clean. Your entities are beautifully mapped. Everything works. And then production traffic hits... and your database starts crying quietly in the corner.

Let's break down what the N+1 problem is and how to solve it in Doctrine ORM using different fetch strategies.

---

What Is the N+1 Problem?

The N+1 problem happens when:

1. You run 1 query to fetch a list of entities.
2. Then you trigger N additional queries --- one for each entity --- to load related data.

Example Scenario

Let's say we have two entities:

• User
• Post

A user can have many posts (OneToMany).

---

Entity Mapping

// src/Entity/User.php

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

    #[ORM\Column]
    private string $name;

    #[ORM\OneToMany(mappedBy: 'user', targetEntity: Post::class)]
    private Collection $posts;
}

// src/Entity/Post.php

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

    #[ORM\Column]
    private string $title;

    #[ORM\ManyToOne(targetEntity: User::class, inversedBy: 'posts')]
    private User $user;
}

---

Problematic Code

$users = $userRepository->findAll();

foreach ($users as $user) {
    echo $user->getName();

    foreach ($user->getPosts() as $post) {
        echo $post->getTitle();
    }
}

What Happens Under the Hood?

1 query:

SELECT * FROM user;

Then for each user:

SELECT * FROM post WHERE user_id = ?;

If you have 100 users → 101 queries.

That's the N+1 problem.

---

Why Does This Happen?

By default, Doctrine uses LAZY loading for associations.

Related data is loaded only when accessed. Sounds efficient, but inside loops, it becomes dangerous.

---

Solutions in Doctrine

1️⃣ Fetch Join (Recommended)

$query = $entityManager->createQuery(
    'SELECT u, p
     FROM App\Entity\User u
     LEFT JOIN FETCH u.posts p'
);

$users = $query->getResult();

This generates one SQL query with a JOIN and eliminates additional queries.

---

2️⃣ QueryBuilder with Fetch Join

public function findAllWithPosts(): array
{
    return $this->createQueryBuilder('u')
        ->leftJoin('u.posts', 'p')
        ->addSelect('p')
        ->getQuery()
        ->getResult();
}

addSelect('p') is crucial for proper hydration.

---

3️⃣ EAGER Fetching (Mapping-Level)

#[ORM\OneToMany(
    mappedBy: 'user',
    targetEntity: Post::class,
    fetch: 'EAGER'
)]
private Collection $posts;

Simple but applies globally --- use carefully.

---

4️⃣ EXTRA_LAZY (Large Collections)

#[ORM\OneToMany(
    mappedBy: 'user',
    targetEntity: Post::class,
    fetch: 'EXTRA_LAZY'
)]

Calling:

$user->getPosts()->count();

Triggers a lightweight COUNT(*) query instead of loading the full collection.

---

5️⃣ Partial Objects

$query = $entityManager->createQuery(
    'SELECT PARTIAL u.{id, name}
     FROM App\Entity\User u'
);

Reduces hydration overhead, but use carefully.

---

6️⃣ Batch Fetching

Config:

doctrine:
  orm:
    entity_managers:
      default:
        default_batch_fetch_size: 20

This converts multiple single-ID queries into batched IN (...) queries.

---

Performance Overview

Strategy Queries Flexibility Risk

---

LAZY N+1 High High JOIN FETCH 1 High Medium EAGER 1 Low Medium EXTRA_LAZY Optimized High Low Batch Fetching Few Medium Low

---

Best Practices

• Default to LAZY
• Use JOIN FETCH when needed
• Avoid global EAGER unless necessary
• Use EXTRA_LAZY for large collections
• Enable batch fetching for ManyToOne
• Always profile queries

---

Final Thoughts

The N+1 problem is not a Doctrine bug --- it's a tradeoff of lazy loading.

The key is being explicit about your data access patterns and choosing the right fetch strategy for each use case.