Doctrine Paginator for Fetch-Joined Collections: An Architecture Note

The failure mode: LIMIT truncates rows, not entities

When a DQL query fetch-joins a to-many association, the SQL result set is flattened. A root entity with five children occupies five rows. Applying setFirstResult() and setMaxResults() to that query limits rows, so a page size of 10 can return a single root entity holding a partial collection, while other roots that should have appeared on the page never do. Doctrine hydrates what the database returned; it has no way to know the collection was cut short.

The symptom is quiet: no exception, no warning, just pages whose collections are incomplete and whose totals do not match the underlying data. This note covers the smallest design that avoids it, the trust boundary around page parameters, and the conditions under which the design should change.

Requirements worth writing down first

• Every root entity on a page must arrive with its fetch-joined collection fully hydrated.
• The total used for page links must be derived from the same filters as the page query, so the two cannot drift.
• Page size must be bounded by the application, not by the caller.
• The design must not reintroduce an N+1 query pattern for the collection.

The smallest suitable design

Doctrine ships Doctrine\ORM\Tools\Pagination\Paginator for exactly this case. It wraps an existing Query object; you still express filters, ordering, and the fetch join in the query builder, and you still set the offset and limit there. The Paginator changes how that query is executed, not how it is written.

// src/Repository/UserRepository.php
public function findActivePage(int $page, int $size): Paginator
{
    $qb = $this->createQueryBuilder('u')
        ->addSelect('o')
        ->innerJoin('u.orders', 'o')
        ->where('u.status = :status')
        ->setParameter('status', 'active')
        ->orderBy('u.id', 'ASC')
        ->setFirstResult(($page - 1) * $size)
        ->setMaxResults($size);

    return new Paginator($qb->getQuery());
}

Run this from a repository class with access to the entity manager. The returned object is iterable, so the controller can pass it straight to a template, and count($paginator) yields the total for page links. No hand-written COUNT DQL is required, which removes the usual source of drift between a list query and its count.

What the Paginator executes

Internally the wrapper rewrites the query into a two-step process. The first statement selects the distinct identifiers of the root entities that fall inside the offset and limit. The second hydrates full entities for those identifiers, with the fetch join applied, so collections are complete. Confirm the exact SQL shape against your installed version rather than assuming it.

Trust boundary: page and size are request input

Page number and page size normally arrive from a query string, which makes them untrusted. Validate them in the controller before they reach the repository:

• Cast to integer and reject non-numeric input rather than coercing silently.
• Clamp the page to a minimum of 1 and the size to a maximum the application chooses, for example 100. An unbounded size lets a caller request a page large enough to exhaust PHP memory during hydration.
• Bind values as parameters. Never concatenate them into DQL, and never use them to build an ORDER BY column name from raw input.

The repository can then assume a sane page and size, which keeps the clamping rule in one place instead of scattered across call sites.

Operational checks

1. Collection integrity test. Create one root entity with three children in a fixture, paginate with size 1, and assert that the single returned root has all three children hydrated. This is the test that fails loudly if someone removes the Paginator wrapper later.
2. SQL log inspection. Enable the SQL logger and confirm two statements per page: one selecting distinct root identifiers with limit and offset, and one restricted to those identifiers. The exact text depends on your ORM version and platform, so read the shape rather than matching a literal string.
3. Count agreement. Compare count($paginator) against a manual COUNT over the same filters on a known fixture. A mismatch usually means the count query and the page query no longer share their filters.
4. Deep-page timing. Load the deepest page you expect users to reach and measure it. Offset cost grows with the offset, so this number decides whether the current design is still adequate.

Failure modes to plan for

• Wrapper omitted. The most common regression. The page looks plausible and is wrong. The integrity test above is the guard.
• Multiple to-many fetch joins. Joining two collections in one query multiplies rows regardless of pagination strategy. Fetch one collection eagerly and load the second with a separate query or a batch fetch.
• Deep offsets. The database scans and discards preceding rows, so page 10,000 costs far more than page 1 even though the page size is unchanged.
• Expensive totals. On large filtered tables the count query can dominate request time, especially when it runs on every page view.
• Scalar result sets. The Paginator expects the query to select the root entity. Queries projecting individual columns are outside its scope and should keep plain limit and offset.

Conditions that would change the design

• Deep paging becomes routine. Move to keyset pagination on a stable, unique sort key, filtering with a comparison against the last seen value instead of an offset. This trades the ability to jump to an arbitrary page for roughly constant cost per page.
• Exact totals are too slow. Drop the total from the interface, show a next-page link only, or cache the count for a short interval and accept slight staleness.
• Only scalar columns are needed. Skip the entity hydration path entirely; there are no collections to truncate.

Version assumptions and what to verify

The class lives at Doctrine\ORM\Tools\Pagination\Paginator in the doctrine/orm 2.x and 3.x lines, but constructor arguments, available hints, and the generated SQL differ between majors and between DBAL platforms. Check the class signature in the installed version before copying the snippet, and treat any output-walker or distinct hint as version-specific until confirmed. The checks in the previous section are the practical way to verify behavior on your own stack; none of them require trusting this note over your own SQL log.