Efficient Pagination with Doctrine QueryBuilder: Practical Tips

Problem: Paginating Large Result Sets in Doctrine

When a PHP application needs to display thousands of records, fetching all rows at once can exhaust memory and increase response time. Doctrine ORM’s QueryBuilder lets you build DQL queries programmatically, but you must add the right clauses and hydration choices to make pagination efficient.

Thesis: Use QueryBuilder for LIMIT/OFFSET, Tune Hydration, and Prevent N+1

By chaining setFirstResult() and setMaxResults() on a QueryBuilder you push the pagination work to the database via SQL LIMIT and OFFSET. Pairing this with scalar hydration or join‑fetch keeps memory low and query counts stable across pages.

1. Building a Reusable Paginated QueryBuilder

A repository method can encapsulate the pagination logic and accept dynamic filters.

public function findUsersPage(int $page, int $limit, bool $activeOnly = false): array
{
    $offset = ($page - 1) * $limit;
    $qb = $this->getEntityManager()->createQueryBuilder();
    $qb->select('u')
       ->from(User::class, 'u')
       ->orderBy('u.createdAt', 'DESC')
       ->setFirstResult($offset)
       ->setMaxResults($limit);

    if ($activeOnly) {
        $qb->andWhere('u.isActive = :active')
           ->setParameter('active', true);
    }

    return $qb->getQuery()->getResult();
}

The setFirstResult and setMaxResults calls become SQL like:

SELECT u.* FROM user u ORDER BY u.createdAt DESC LIMIT 50 OFFSET 150

Replace the numbers with the variables.

2. Reducing Memory with ArrayHydrator

If you only need a few columns, avoid full entity hydration.

$qb->select('u.id', 'u.username', 'u.email')
   ->setHint(Query::HINT_HYDRATE_MODE, Query::HYDRATE_ARRAY);
$users = $qb->getQuery()->getResult();

Benefits: lower memory per row and faster execution because Doctrine skips entity lifecycle. The trade‑off is losing lazy loading; use this only when the data is read‑only.

3. Avoiding N+1 with JOIN FETCH

Accessing a lazy association after pagination can trigger a query per row. Fetch the association eagerly.

$qb->select('u', 'p')
   ->from(User::class, 'u')
   ->leftJoin('u.profile', 'p')
   ->addSelect('p')
   ->setFirstResult($offset)
   ->setMaxResults($limit);

Doctrine will generate a LEFT JOIN and hydrate both User and Profile in one round‑trip. Verify the generated SQL with Doctrine’s SQL logger or a profiler to ensure no extra queries appear.

4. Trade‑offs and Limitations

• Large Offsets Are Costly: For offsets beyond ~10,000 rows the database may scan many rows before applying LIMIT, making the query slower. Consider keyset pagination (seek method) for deep paging.
• Lazy Loading After the Page: If you access an association that was not joined, each row will cause an additional query.
• Full Entity Hydration Overhead: Creating proxies and loading lifecycle events adds memory and CPU cost.

Worked Example: PHPUnit Test for Pagination

This test checks that the query includes LIMIT and OFFSET and measures memory usage.

public function testPaginationReturnsCorrectSlice(): void
{
    $repo = $this->getEntityManager()->getRepository(User::class);
    $page = $repo->findUsersPage(2, 20); // second page, 20 per page

    $this->assertCount(20, $page);

    // Check memory usage (optional)
    $start = memory_get_peak_usage();
    $repo->findUsersPage(2, 20);
    $end = memory_get_peak_usage();
    $this->assertLessThan(2 * 1024 * 1024, $end - $start); // example threshold

    // Verify SQL contains LIMIT and OFFSET
    $sqlLogger = $this->getEntityManager()->getConnection()->getConfiguration()->getSQLLogger();
    $queries = $sqlLogger->getQueries();
    $lastQuery = end($queries);
    $this->assertStringContainsString('LIMIT 20', $lastQuery['sql']);
    $this->assertStringContainsString('OFFSET 20', $lastQuery['sql']);
}

Run the test with PHPUnit; adjust the memory assertion to match your environment. The test does not modify data, so no rollback is needed.

Actionable Closing

Encapsulate pagination in a repository method, choose the hydration mode that matches your data needs, join‑fetch any associations you will use, and watch out for large offsets. A simple PHPUnit test can confirm that the generated SQL contains LIMIT and OFFSET and that memory stays within expectations.