Pagination with Doctrine QueryBuilder for Large Result Sets

The problem: loading everything into memory

When you fetch thousands of entities with a plain Doctrine QueryBuilder, the ORM hydrates every row into PHP objects. On a typical web request this can quickly exhaust RAM, slow down the response, or trigger an out‑of‑memory error—especially if the query includes fetch‑joined collections that cause duplicate rows.

Takeaway: slice the result set early

By applying setFirstResult and setMaxResults (or wrapping the query in Doctrine\ORM\Tools\Pagination\Paginator) you limit the SQL to a single page. Memory usage stays predictable and the query stays fast, while a separate count query gives you the total number of pages.

Building a paginated QueryBuilder

Start with a QueryBuilder that defines the data set you want to page—filters, ordering, and any needed joins.

// $entityManager is injected; run this inside a service or controller
$qb = $entityManager->createQueryBuilder()
    ->select('p')
    ->from('App\Entity\BlogPost', 'p')
    ->where('p.published = :pub')
    ->setParameter('pub', true)
    ->orderBy('p.createdAt', 'DESC');

Now apply the slice for the desired page (page numbers start at 1).

$page    = 3;          // current page
$pageSize = 20;        // items per page
$offset   = ($page - 1) * $pageSize;

$query = $qb->getQuery()
            ->setFirstResult($offset)
            ->setMaxResults($pageSize);

$posts = $query->getResult(); // array of BlogPost entities

Place this code anywhere you have access to the EntityManager (e.g., a Symfony service). No special DB permissions are required beyond the usual SELECT rights.

When you need fetch joins: use the Doctrine Paginator

If your query includes a fetch‑joined collection (e.g., LEFT JOIN p.comments c), the raw SQL may return duplicate root rows. The Doctrine\ORM\Tools\Pagination\Paginator solves this by issuing a DISTINCT identifier query internally.

$qb = $entityManager->createQueryBuilder()
    ->select('p, c')
    ->from('App\Entity\BlogPost', 'p')
    ->leftJoin('p.comments', 'c')
    ->where('p.published = :pub')
    ->setParameter('pub', true)
    ->orderBy('p.createdAt', 'DESC');

$paginator = new \\Doctrine\\ORM\\Tools\\Pagination\\Paginator($qb->getQuery(), $fetchJoinCollection = true);
$paginator->getQuery()
          ->setFirstResult(($page - 1) * $pageSize)
          ->setMaxResults($pageSize);

$posts = iterator_to_array($paginator); // still an array of BlogPost

The Paginator adds one extra query to fetch the distinct IDs, but it prevents duplicate hydrated entities.

Getting the total count for UI pagination

To render page numbers you need the total number of matching rows. Run a separate count query that reuses the same WHERE clauses but omits ordering and fetch joins.

$countQb = $entityManager->createQueryBuilder()
    ->select('COUNT(p.id)')
    ->from('App\Entity\BlogPost', 'p')
    ->where('p.published = :pub')
    ->setParameter('pub', true);

$totalItems = (int) $countQb->getQuery()->getSingleScalarResult();
$totalPages = (int) ceil($totalItems / $pageSize);

On very large tables this count can become a bottleneck; consider caching the result or approximating it when exact numbers are not critical.

Verification and practical checks

1. Enable Doctrine’s SQL logger (e.g., in dev environment) and watch the generated queries: you should see a SELECT … LIMIT ? OFFSET ? for the data fetch and a separate SELECT COUNT(*) … for the total.
2. Check memory usage before and after the request with memory_get_usage(); it should stay roughly constant regardless of total rows.
3. Confirm that the number of returned entities equals $pageSize (except possibly on the last page) and that the sum across pages matches the count query.

Limitations to keep in mind

• Fetch‑joined pagination via Paginator adds an extra query and slightly more complexity.
• The count query can be expensive on huge tables; evaluate whether an exact total is required.
• If you change the ordering or add new WHERE clauses, remember to update both the data query and the count query to keep them in sync.

Actionable closing

Start by wrapping any list‑type query in the pattern shown above: build your QueryBuilder, apply setFirstResult/setMaxResults, and if you need fetch joins, use the Doctrine Paginator. Verify the SQL output and memory usage, then you’ll have a scalable, predictable way to serve large result sets without exhausting your server’s RAM.