DQL JOIN FETCH or fetch=EAGER mapping for a collection-heavy entity that is sometimes only counted

Question

ScenarioAn application built on Doctrine ORM has an entity with a large one-to-many collection. Some code paths iterate the full collection, while others only need its size. The default lazy loading triggers one extra query per access, and in detached states it risks a LazyInitializationException, so a more deliberate fetching strategy is needed.The trade-offSetting fetch: EAGER in the mapping removes the N+1 pattern everywhere but loads the collection even when only a count is required, increasing memory use and duplicating root rows in the SQL result set. Using DQL JOIN FETCH per query keeps the mapping neutral but must be repeated at every call site. The EXTRA_LAZY fetch mode appears to cover the count-only paths, since operations like count() can run as a SELECT COUNT without hydrating the collection.QuestionsFor an entity that is both fully iterated and only counted, is combining EXTRA_LAZY mapping with explicit JOIN FETCH queries preferable to a global fetch: EAGER?Does EXTRA_LAZY compose correctly with JOIN FETCH in the same query, or do the two modes conflict?Besides enabling the SQL logger and comparing memory against a plain find(), what practical checks confirm the chosen strategy actually reduces query count without hydrating the collection on count-only paths?

Tasadduq Burney · Answer

For an entity that is both fully iterated and only counted, **combining `EXTRA_LAZY` mapping with explicit `JOIN FETCH` queries is preferable to global `fetch: EAGER`**.

Global `fetch: EAGER` is a blunt instrument; it forces the collection to be loaded every time the root entity is retrieved, leading to high memory consumption and row duplication in the SQL result set when you only need a count. In contrast, `EXTRA_LAZY` allows Doctrine to perform operations like `count()`, contains()`, or slice() using targeted SQL queries without hydrating the entire collection.

Interaction between Fetch Modes
The modes EXTRA_LAZY and JOIN FETCH do not conflict; they operate at different layers. When you use JOIN FETCH in a DQL query, it overrides the mapping's fetch mode for that specific execution, hydrating the collection in a single query. If you not use JOIN FETCH, the EXTRA_LAZY mapping takes over, ensuring that count() calls execute a SELECT COUNT(...) rather than loading all entities into memory.

Recommended Strategy

Set the collection mapping to fetch="EXTRA_LAZY" in the entity definition. This ensures that the "count-only" paths remain efficient by default.
  Use DQL JOIN FETCH in specific repository methods where you know full iteration is required. This prevents N+1 issues for those specific cases.
  Use $collection->count() in the count-only paths to trigger the EXTRA_LAZY SQL-level count.

Practical Verification Checks
To confirm your strategy is working as intended without unintended hydration, perform these checks:

Query Count Assertion: Use the Symfony Profiler or SQL logger to verify that the count-only path triggers exactly one query (the count) and zero queries for the collection members.
  Hydration Check: For the iteration path, verify that a single query is executed with a LEFT JOIN and that accessing the collection elements does not trigger subsequent lazy loads.
  Memory Profiling: Compare memory_get_peak_usage() between a standard find() and your count-only logic. If memory spikes significantly during the count-only path, the collection is likely being hydrated unintentionally.

Assumption: This assumes you are using Doctrine ORM 2.x+. If the entity is detached, EXTRA_LAZY operations will still throw a LazyInitializationException unless the collection was explicitly fetched before detachment.

DQL JOIN FETCH or fetch=EAGER mapping for a collection-heavy entity that is sometimes only counted

Scenario

The trade-off

Questions

1 answer

Interaction between Fetch Modes

Recommended Strategy

Practical Verification Checks

Your perspective belongs here.

0 question comments