Efficient Pagination in Doctrine ORM: Beyond Basic Offsets

The Performance Wall of Offset Pagination

When building a list view for a large dataset—such as a user directory or a transaction log—the default instinct is to use a page number and a limit. In Doctrine, this typically means calling setFirstResult() and setMaxResults(). While this works for the first few pages, it creates a hidden performance bottleneck: the database must still scan and discard all preceding rows to reach the requested offset.

As your table grows to hundreds of thousands of rows, requesting page 1,000 becomes significantly slower than requesting page 1. To solve this, engineers must choose between traditional offset-based pagination and keyset (cursor) pagination, while ensuring the underlying database indexes support the query structure.

Implementing Offset Pagination

Offset pagination is the standard for UIs that require specific page numbers. In Doctrine 2.x, this is handled via the QueryBuilder. The critical requirement is a deterministic orderBy clause; without it, the database may return rows in a different order across requests, causing items to disappear or duplicate across pages.

// Run this within a Service or Repository context
// Required permissions: Read access to the User entity
$limit = 20;
$page = 3; // 1-based index from request
$offset = ($page - 1) * $limit;

$qb = $this->entityManager->createQueryBuilder();
$qb->select('u')
   ->from(User::class, 'u')
   ->where('u.active = :active')
   ->setParameter('active', 1)
   ->orderBy('u.id', 'ASC') // Essential for stability
   ->setFirstResult($offset)
   ->setMaxResults($limit);

$results = $qb->getQuery()->getResult();

Verification: To confirm this is working efficiently, enable the SQL logger in your development environment. Verify that the generated SQL contains a LIMIT and OFFSET clause. If the query is slow, run EXPLAIN on the generated SQL to ensure a composite index exists on (active, id).

Scaling with Keyset (Cursor) Pagination

For very large datasets or "infinite scroll" interfaces, cursor-based pagination is superior. Instead of telling the database how many rows to skip, you tell it exactly where to start based on the last seen record. This allows the database to jump directly to the next set of rows using an index.

In Doctrine 2.15+, while setCursor() provides internal support, the practical engineering pattern is to filter by the unique identifier of the last item from the previous page.

// Example: Fetching the next page after ID 500
$lastSeenId = $request->query->get('after'); // e.g., 500
$limit = 20;

$qb = $this->entityManager->createQueryBuilder();
$qb->select('u')
   ->from(User::class, 'u')
   ->where('u.active = :active')
   ->setParameter('active', 1)
   ->orderBy('u.id', 'ASC');

if ($lastSeenId) {
    $qb->andWhere('u.id > :lastId')
       ->setParameter('lastId', $lastSeenId);
}

$qb->setMaxResults($limit);
$results = $qb->getQuery()->getResult();

The Join Inflation Pitfall

A common failure occurs when paginating queries that use JOIN on one-to-many or many-to-many relationships. If a User has five Roles and you JOIN them, the database returns five rows for one User. setMaxResults(10) might then only return two actual User entities because the result set is inflated by the joined roles.

To fix this, use the Doctrine\ORM\Tools\Pagination\Paginator utility. The Paginator wraps the query and performs a separate COUNT and a distinct ID fetch to ensure that setMaxResults applies to the root entity, not the total number of joined rows.

Trade-offs: Offset vs. Cursor

Feature	Offset Pagination	Cursor Pagination
UI Capability	Jump to specific page (e.g., Page 5)	Next/Previous or Infinite Scroll
Performance	Degrades as offset increases	Constant time (O(1) with index)
Consistency	Items shift if rows are deleted/added	Stable; no skipped/duplicate items

Practical Implementation Checklist

• Index your filters: Ensure any column in the where() clause and the orderBy() column are indexed together.
• Avoid off-by-one: Always calculate offset as (page - 1) * limit since setFirstResult is zero-indexed.
• Use Paginator for Joins: If your query involves leftJoin or innerJoin on collections, wrap the query in the Paginator class.
• Test deep pages: Don't just test page 1. Test page 100 to see if latency spikes, which indicates a need for cursor-based logic.