Using Doctrine Paginator for Safe Pagination with Joined Collections

Why you need the Paginator

When you query a parent entity while fetching a related collection (e.g., User with its Post entities) using a FETCH JOIN, a single parent row can appear multiple times in the result set—once for each child. Applying setFirstResult() and setMaxResults() directly to the QueryBuilder therefore limits rows, not distinct parents, leading to missing or duplicated entities and an incorrect total count.

How the Paginator works

The Doctrine\ORM\Tools\Pagination\Paginator solves this by executing three steps internally:

1. Count query – selects distinct identifiers of the root entity, respecting all WHERE, GROUP BY, and HAVING clauses.
2. ID fetch query – retrieves the identifiers for the current page using LIMIT and OFFSET on the distinct ID set.
3. Entity hydration query – loads the full entities (with joins) where the root identifier is IN the set of IDs from step 2.

This guarantees that each root entity is counted once, regardless of how many joined rows it produces.

Worked example

Assume a bidirectional OneToMany relationship: User has many Post entities.

// src/Repository/UserRepository.php
use Doctrine\ORM\EntityRepository;
use Doctrine\ORM\Tools\Pagination\Paginator;

class UserRepository extends EntityRepository
{
    public function getUsersWithPostsPaginated(int $page, int $limit): Paginator
    {
        $qb = $this->createQueryBuilder('u')
            ->leftJoin('u.posts', 'p')          // FETCH JOIN implicitly via addSelect
            ->addSelect('p')                    // ensure posts are hydrated
            ->orderBy('u.id', 'ASC');

        $query = $qb->getQuery();

        // Paginator handles the pagination logic
        $paginator = new Paginator($query, $fetchJoinCollection = true);

        // Set pagination offsets on the Paginator, not on the raw Query
        $paginator->getQuery()
            ->setFirstResult(($page - 1) * $limit)
            ->setMaxResults($limit);

        return $paginator;
    }
}

Usage in a service or controller:

$usersPaginated = $userRepo->getUsersWithPostsPaginated($page = 2, $limit = 10);
foreach ($usersPaginated as $user) {
    // $user->getPosts() is already initialized
    echo $user->getUsername(), ' has ', count($user->getPosts()), ' posts\n';
}
// Total count of distinct users, respecting joins
$totalUsers = $usersPaginated->count();

Verification steps

To confirm the Paginator is generating the expected three queries:

• Enable Doctrine’s SQL logger or use a database profiler.
• Run the repository method above.
• Observe the logged SQL: first a SELECT DISTINCT u0_.id ... (count query), second a SELECT u0_.id FROM (...) LIMIT ? OFFSET ? (ID fetch), and third the full hydrating query with WHERE u0_.id IN (?, ?, ...).
• Compare the row count of the raw Query with setFirstResult/setMaxResults (which will be higher or lower than expected) to the $paginator->count() value.

Limitations and common mistakes

• Performance overhead – The Paginator executes at least two extra queries. For very large tables ensure the columns used in the WHERE clause (especially on joined entities) are indexed; otherwise the count query can become a bottleneck.
• Incorrect usage – Applying setFirstResult and setMaxResults directly to the QueryBuilder before passing it to the Paginator bypasses the ID‑fetch step and yields wrong results.
• Complex WHERE on joined entities – If your filter references columns from the joined table (e.g., WHERE p.title LIKE :search), the count query must also evaluate that condition, which may prevent the use of certain indexes. Review the generated count SQL and add appropriate indexes or consider denormalizing the filter column.
• Fetch join collection flag – When constructing the Paginator, pass $fetchJoinCollection = true (as shown) to tell Doctrine to hydrate collections correctly. Omitting this flag can cause lazy‑loading issues.
• Database vendor specifics – Some vendors (e.g., Oracle) require special handling for DISTINCT with LIMIT. The Paginator abstracts these differences, but keep your Doctrine version up‑to‑date (≥ 2.8) to benefit from vendor‑specific fixes.