Doctrine ORM: How I Escaped the Cartesian Product Trap

#symfony #doctrine #performance #softwareengineering

Snippet from a client project; property names changed for confidentiality.
I started by opening the Symfony profiler on my catalog page and there it was: 201 SQL queries. For just 50 products. Development load time? 3 seconds. In production, that’s a ticking time bomb, waiting for the first traffic spike to explode.

If you use Doctrine, you’ve probably run into the N+1 monster. But in the rush to defeat it, many developers stumble into an even more vicious "final boss": the Cartesian Product.

Here’s how I optimized hydration without killing performance.

1. Diagnosing N+1 in the Wild

My Product entity is standard but data-heavy, with four main relationships:

categories (ManyToMany)
tags (ManyToMany)
images (OneToMany)
reviews (OneToMany)

The code looks innocent enough:

$products = $productRepository->findBy(['active' => true]);

But in the Twig template, it’s a bloodbath. Every time the template accesses product.categories or product.tags, Doctrine hits the database again.

Quick math: 1 initial query (products) + (50 products × 4 relations) = 201 queries.

2. The Pitfalls of EAGER Fetching with Collections

My first instinct? Set the relations to fetch: 'EAGER'. Result? The same number of queries—just triggered earlier, in the controller instead of the template.

Lesson learned: EAGER fetching works well for ToOne relationships (ManyToOne, OneToOne) but behaves differently with collections (OneToMany, ManyToMany). For collections, Doctrine typically runs extra queries per association—essentially "N+1 in disguise."

Note: LAZY vs EAGER controls when data is loaded, not always how. For collections, EAGER rarely solves N+1; it just moves the timing around.

3. The Hidden Cost of Cartesian Products

"Fine," I thought, "I’ll just leftJoin and addSelect everything in a single Query Builder call."

$this->createQueryBuilder('p')
    ->leftJoin('p.categories', 'c')->addSelect('c')
    ->leftJoin('p.tags', 't')->addSelect('t')
    ->leftJoin('p.images', 'i')->addSelect('i')
    ->leftJoin('p.reviews', 'r')->addSelect('r');

Profiler shows "1 query." Victory? Not really. The page was slower. The culprit: the Cartesian Product.

Imagine a product with 3 categories and 4 images. SQL can’t return nested arrays in a column, so it flattens everything:

3 categories × 4 images = 12 rows for one product
Add 5 tags and 10 reviews: 3 × 4 × 5 × 10 = 600 rows for a single product

For 50 products, that’s over 30,000 rows. Doctrine chokes trying to deduplicate this data to hydrate your objects. Multiplicative explosion occurs whenever multiple collections are joined.

4. The Solution: Multi-Step Hydration

This is where Doctrine’s Identity Map shines. Instead of one massive join, break the loading into logical steps:

public function findActiveWithRelations(): array
{
    // Step 1: Load products + categories
    $products = $this->createQueryBuilder('p')
        ->addSelect('c')
        ->leftJoin('p.categories', 'c')
        ->where('p.active = true')
        ->getQuery()
        ->getResult();

    if (empty($products)) return [];

    // Step 2: Prime tags into the Identity Map
    $this->createQueryBuilder('p')
        ->addSelect('t')
        ->leftJoin('p.tags', 't')
        ->where('p.active = true')
        ->getQuery()
        ->getResult();

    // Step 3: Prime images
    $this->createQueryBuilder('p')
        ->addSelect('i')
        ->leftJoin('p.images', 'i')
        ->where('p.active = true')
        ->getQuery()
        ->getResult();

    // Step 4: Prime reviews
    $this->createQueryBuilder('p')
        ->addSelect('r')
        ->leftJoin('p.reviews', 'r')
        ->where('p.active = true')
        ->getQuery()
        ->getResult();

    return $products;
}

Why This Works

Query 1: Loads 50 products + categories.
Subsequent queries: Load related entities. Doctrine tracks product IDs in memory via the Identity Map. Even if you don’t assign the result, Doctrine hydrates the entities and attaches relations to the existing objects.

Result: 4 lightweight, predictable queries, no data duplication.
Pro Tip: Make sure each step uses the exact same WHERE filters. For complex cases (e.g., pagination), it’s safer to extract IDs from the first query and use WHERE p.id IN (:ids) for subsequent queries.

5. Automate to Prevent Regressions

Tracking queries manually is tedious. I now use doctrine-doctor to stay ahead.

It integrates with the Symfony Web Profiler and alerts you to:

Obvious N+1 patterns
Excessive hydration (Cartesian products)
Missing indexes on join columns

composer require --dev ahmed-bhs/doctrine-doctor

Final Lesson: In ORM optimization, "less" isn’t always "better." Reducing 201 queries to 1 can crash your server via a Cartesian product. Reducing 201 to 4 can save it.

References: