Efficient Pagination in Doctrine ORM Using QueryBuilder and Paginator

Problem: Fetching large lists without killing memory or DB

When building APIs, admin grids, or any UI that shows long lists, loading every row into memory is wasteful and slows down the response. Doctrine ORM lets you retrieve only the slice you need, but you must combine a QueryBuilder with the Paginator wrapper to get both the limited result set and an accurate total count.

Thesis: Use Doctrine's QueryBuilder with the Paginator wrapper to get efficient, paginated result sets

The QueryBuilder lets you construct DQL safely and reuse it across services. Wrapping that query in Doctrine\ORM\Tools\Pagination\Paginator adds automatic LIMIT/OFFSET handling and a separate count query (or a single optimized query when the pagination walker can eliminate the extra count). This pattern keeps memory usage low and gives you the total number of rows for UI pagination controls.

How it works

1. Build a QueryBuilder that defines the base SELECT, joins, WHERE clauses, and ordering.
2. Call getQuery() to obtain a Doctrine\ORM\AbstractQuery.
3. Apply setFirstResult($offset) and setMaxResults($limit) to the query.
4. Pass the query to new Paginator($query, $fetchJoinCollection = true).
5. The paginator executes two queries when needed:
   • A limited SELECT that returns only the current page of entities.
   • A COUNT query (or an optimized single query) to determine $paginator->count().
6. Iterate over the paginator to get the entities for the page.

Worked example

Suppose we have an Article entity and we want to return 50 articles per page, ordered by ID.

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

class ArticleRepository extends EntityRepository
{
    /**
     * @return Paginator  Iterable collection of Article entities for the requested page
     */
    public function getPaginated(int $page, int $limit): Paginator
    {
        // 1️⃣ Build the base DQL
        $qb = $this->createQueryBuilder('a')
                   ->orderBy('a.id', 'ASC');

        // 2️⃣ Prepare the query
        $query = $qb->getQuery();
        $query->setFirstResult(($page - 1) * $limit)
              ->setMaxResults($limit);

        // 3️⃣ Wrap in Paginator – the second argument enables fetching of join collections
        return new Paginator($query, $fetchJoinCollection = true);
    }
}

Using the repository from a controller (or service):

// src/Controller/ArticleController.php
use Symfony\Bundle\FrameworkBundle\Controller\AbstractController;
use Symfony\Component\HttpFoundation\JsonResponse;

class ArticleController extends AbstractController
{
    public function index(int $page = 1, ArticleRepository $repo): JsonResponse
    {
        $paginator = $repo->getPaginated($page, 50);

        $items = [];
        foreach ($paginator as $article) {
            $items[] = [
                'id'   => $article->getId(),
                'title'=> $article->getTitle(),
                // … other fields
            ];
        }

        return new JsonResponse([
            'data'  => $items,
            'total' => $paginator->count(),
            'page'  => $page,
            'limit' => 50
        ]);
    }
}

Verification: Confirm the generated SQL

Enable Doctrine's SQL logger (or use your framework's debug toolbar) and request a page. You should see output similar to:

SELECT a0_.id AS id_0, a0_.title AS title_1, ...
FROM article a0_
ORDER BY a0_.id ASC
LIMIT 50 OFFSET 0

SELECT COUNT(DISTINCT a0_.id) AS sclr_0
FROM article a0_
ORDER BY a0_.id ASC

If the pagination walker can optimize (e.g., no fetch joins that break counting), you may see only the limited SELECT with a embedded count sub‑query instead of a separate COUNT.

To verify deep‑page performance, request a high page number (e.g., page 100) and measure the response time. Compare it with a keyset‑based approach (see limitation below).

Trade‑off and limitation: OFFSET cost for deep pagination

The LIMIT/OFFSET strategy forces the database to skip over all preceding rows. As the offset grows, the query scans more rows, increasing latency and CPU usage. For APIs that may be asked for page 1000 or beyond, this becomes a bottleneck.

When to consider keyset (seek) pagination:

• When the total row count is large (> 100k) and users frequently request deep pages.
• When you can order by a unique, indexed column (e.g., id or a timestamp).
• When you can replace the offset with a “where id > :lastSeenId” clause.

Keyset pagination avoids the extra scan but requires the client to pass the last seen identifier and does not give a direct total count without an additional query.

Actionable closing

1. Start with the QueryBuilder → Paginator pattern for simple lists and admin grids.
2. Enable the SQL logger in dev/staging to confirm that only one limited SELECT and one COUNT (or an optimized single query) are executed.
3. Monitor response times as you increase the page number; if latency grows linearly with offset, evaluate keyset pagination for those endpoints.
4. Document the chosen pagination strategy in your service’s API documentation so consumers know whether they can rely on total values.

By following these steps you get a predictable, memory‑efficient way to paginate Doctrine entities while keeping an eye on the performance implications of large offsets.