Pagination

Doctrine ORM provides two pagination strategies for DQL queries. Both handle the low-level SQL plumbing, but they make different trade-offs:

Feature	`OffsetPaginator`	`CursorPaginator`
Total count	Yes (extra query)	Yes (extra query)
Random access to page N	Yes	No
Stable under concurrent inserts/deletes	No	Yes
Performance on deep pages	Degrades (OFFSET scan)	Constant (index range scan)
Requires deterministic ORDER BY	No	Yes

Choose the Offset Paginator when you need random access to an arbitrary page number.

Choose the Cursor Paginator when you need stable, high-performance pagination on large datasets and a simple previous/next navigation is sufficient. A total count is also available via getTotalCount(), at the cost of an extra COUNT query.

Offset-Based Pagination

Doctrine ORM ships with the OffsetPaginator for offset-based pagination of DQL queries. The query and the pagination position — a Window value object, which carries both a first result and a page size — are both passed to paginate(), which returns an immutable, iterable WindowPage. This mirrors the CursorPaginator API.

<?phpuse Doctrine\ORM\Tools\Pagination\OffsetPaginator;use Doctrine\ORM\Tools\Pagination\Window;$dql   = 'SELECT p, c FROM BlogPost p JOIN p.comments c ORDER BY p.id ASC';$query = $entityManager->createQuery($dql);// new Window($firstResult, $maxResults), or Window::fromPageNumberAndSize($pageNumber, $pageSize)$page = (new OffsetPaginator())->paginate($query, Window::fromPageNumberAndSize(1, 25));echo $page->getTotalCount() . " result(s), page {$page->getPageNumber()} of {$page->getPageCount()}\n";foreach ($page as $post) {    echo $post->getHeadline() . "\n";}if ($page->hasNextPage()) {    $nextWindow = $page->getNextWindow(); // Window for the next page}

The paginator itself holds no query and no position, only configuration: a single instance is stateless and can be reused — or registered as a service — for any query and any page.

<?php$paginator = new OffsetPaginator();$firstPage  = $paginator->paginate($query, Window::fromPageNumberAndSize(1, 25));$secondPage = $paginator->paginate($query, $firstPage->getNextWindow());

Because the returned WindowPage is immutable and carries no temporal coupling, its accessors can be called in any order, and building another page never affects a page you already hold.

How Offset Pagination Works

Paginating Doctrine queries is not as simple as you might think in the beginning. If you have complex fetch-join scenarios with one-to-many or many-to-many associations using the "default" LIMIT functionality of database vendors is not sufficient to get the correct results.

By default the paginator does the following steps to compute the correct result:

Perform a Count query using DISTINCT keyword.
Perform a Limit Subquery with DISTINCT to find all ids of the entity in from on the current page.
Perform a WHERE IN query to get all results for the current page.

This behavior is only necessary if you actually fetch join a to-many collection. You can disable it by setting the fetchJoinCollection constructor argument to false; in that case only 2 instead of the 3 queries described are executed.

fetchJoinCollection set to true might affect results if you use aggregations in your query.

Alternatively, the PaginatorInterface::HINT_ENABLE_DISTINCT query hint instructs Doctrine that the query will not produce "duplicate" rows (only to-one relations are joined), so the DISTINCT keyword is omitted, which can bring important performance improvements:

<?phpuse Doctrine\ORM\Tools\Pagination\OffsetPaginator;use Doctrine\ORM\Tools\Pagination\PaginatorInterface;use Doctrine\ORM\Tools\Pagination\Window;$dql   = 'SELECT u, p FROM User u JOIN u.mainPicture p ORDER BY u.id ASC';$query = $entityManager->createQuery($dql)                       ->setHint(PaginatorInterface::HINT_ENABLE_DISTINCT, false);$page = (new OffsetPaginator())->paginate($query, new Window(0, 100));

API Reference

OffsetPaginator::paginate(Query|QueryBuilder $query, Window $position): WindowPage: Executes the query for the given Window and returns an immutable WindowPage. All page accessors below live on the returned page. Throws an InvalidArgumentException if $position is not a Window.
WindowPage::getItems(): array: Returns the raw entity array for the current page.
WindowPage::count(): int: Returns the number of items on the current page (SPL Countable).
WindowPage::getTotalCount(): int: Returns the total number of matching root entities, ignoring the window.
WindowPage::getPageNumber(): int / WindowPage::getPageCount(): int: Return the 1-based number of the current page and the total number of pages. getPageCount() is at least 1, even for an empty result set.
WindowPage::hasNextPage(): bool / WindowPage::hasPreviousPage(): bool: Return whether a next / previous page is available.
WindowPage::hasToPaginate(): bool: Returns whether the result set spans more than one page.
WindowPage::getNextWindow(): Window / WindowPage::getPreviousWindow(): Window: Return the Window for the next / previous page. Throw a LogicException if there is none — call hasNextPage() / hasPreviousPage() first.
WindowPage::getLastWindow(): Window: Returns the Window of the last page, keeping the same page size. Unlike the two methods above, it never throws: an empty result set has a single, empty first page.
WindowPage::getWindow(): Window: Returns the Window that produced this page.
Window::fromPageNumberAndSize(int $pageNumber, int $pageSize): Window: Builds a Window from a 1-based page number and a page size.
Window::getPageNumber(): int: Returns the 1-based page number the window points at.

Legacy `Paginator`

The legacy Paginator reads the offset implicitly from the query (setFirstResult() / setMaxResults()) and implements the SPL interfaces Countable and IteratorAggregate:

<?phpuse Doctrine\ORM\Tools\Pagination\Paginator;$dql = "SELECT p, c FROM BlogPost p JOIN p.comments c";$query = $entityManager->createQuery($dql)                       ->setFirstResult(0)                       ->setMaxResults(100);$paginator = new Paginator($query, fetchJoinCollection: true);$c = count($paginator);foreach ($paginator as $post) {    echo $post->getHeadline() . "\n";}

Cursor-Based Pagination

Doctrine ORM ships with a CursorPaginator for cursor-based pagination of DQL queries. Unlike offset-based pagination, cursor pagination uses opaque pointers (cursors) derived from the last seen row to fetch the next or previous page. This makes it stable and performant on large datasets — no matter how deep you paginate, the database always uses an index range scan instead of skipping rows.

Cursor pagination requires a deterministic ORDER BY clause. Every column combination used for sorting must uniquely identify a position in the result set. A common pattern is to sort by a timestamp and then by primary key as a tie-breaker.

Constructor

<?phpnew CursorPaginator(    int $limit,    bool $queryProducesDuplicates = true,)

The paginator only holds configuration: the query and the cursor are passed to paginate(), which returns an immutable CursorPage — symmetric with the offset-based OffsetPaginator. A single instance is stateless and can be reused, or registered as a service, for any query and any page.

$limit: The maximum number of results per page. Unlike Window, which carries its own page size, a Cursor is a pure position: the page size is paginator configuration and therefore never comes from user input.
$queryProducesDuplicates: Set to true (default) when the query joins a to-many collection. The paginator then uses a two-query strategy (ID subquery + WHERE IN) to return the correct number of root entities despite duplicate rows. Set to false when only to-one joins are present — this avoids the subquery overhead and is equivalent to passing fetchJoinCollection: false to the OffsetPaginator. However, passing false on a query that joins a to-many relation is not detected — arbitrary joins can produce duplicate root entities silently, leading to a corrupt result set.

Basic Usage

The $position parameter of paginate() accepts either an encoded string produced by a previous call to getNextCursorAsString() or getPreviousCursorAsString(), or a Cursor instance returned by getNextCursor() or getPreviousCursor(). On the first request it is null or an empty string '' — both are treated identically as the first page. It is typically read from the incoming HTTP query string:

$cursor = $_GET['cursor'] ?? null; // null or '' on the first page

<?phpuse Doctrine\ORM\Tools\Pagination\CursorPaginator;$dql = 'SELECT p FROM BlogPost p ORDER BY p.createdAt DESC, p.id DESC';$query = $entityManager->createQuery($dql);$paginator = new CursorPaginator(limit: 15);$page      = $paginator->paginate($query, $cursor);foreach ($page as $post) {    echo $post->getTitle() . "\n";}echo $page->getPreviousCursorAsString(); // previous encoded cursor stringecho $page->getNextCursorAsString();     // next encoded cursor string

Navigating Pages

Pass the encoded cursor back on subsequent requests to move forward or backward, reusing the same paginator:

<?php// Next page$page = $paginator->paginate($query, $nextCursor);// Previous page$page = $paginator->paginate($query, $previousCursor);

The cursor is an encoded string containing the location at which the next query should begin fetching results, along with the navigation direction.

API Reference

CursorPaginator::paginate(Query|QueryBuilder $query, Cursor|string|null $position): CursorPage: Executes the query for the given cursor and returns an immutable CursorPage. Fetches $limit + 1 rows to detect whether a further page exists, then trims the extra row. All accessors below live on the returned CursorPage.
CursorPage::getNextCursor(): Cursor: Returns the Cursor object for the next page. Throws a LogicException if there is no next page — call hasNextPage() first.
CursorPage::getPreviousCursor(): Cursor: Returns the Cursor object for the previous page. Throws a LogicException if there is no previous page — call hasPreviousPage() first.
CursorPage::getNextCursorAsString(): string: Returns the encoded cursor to retrieve the next page. Throws a LogicException if there is no next page — call hasNextPage() first.
CursorPage::getPreviousCursorAsString(): string: Returns the encoded cursor to retrieve the previous page. Throws a LogicException if there is no previous page — call hasPreviousPage() first.
CursorPage::hasNextPage(): bool: Returns whether a next page is available.
CursorPage::hasPreviousPage(): bool: Returns whether a previous page is available.
CursorPage::hasToPaginate(): bool: Returns whether either a next or previous page exists (i.e. the result set spans more than one page).
CursorPage::getItems(): array: Returns the raw entity array for the current page.
CursorPage::getItemsWithCursors(): array: Returns an array of CursorItem objects, each wrapping an entity and its individual Cursor. Useful when you need per-row cursors.
CursorPage::getCursorForItem(mixed $item, bool $isNext = true): Cursor: Builds a Cursor pointing at a specific entity. $isNext = true means "start after this item"; false means "start before this item".
CursorPage::count(): int: Returns the number of items on the current page.
CursorPage::getTotalCount(): int: Executes an extra COUNT query and returns the total number of matching root entities, ignoring the cursor and limit. Use this when you need to display a total result count alongside previous/next navigation.

Next page

SELECT ...FROM   post pWHERE  (p.created_at < :cursor_val_0)   OR  (p.created_at = :cursor_val_0 AND p.id < :cursor_id_1)ORDER  BY p.created_at DESC, p.id DESCLIMIT  16   -- limit + 1

Previous page

SELECT ...FROM   post pWHERE  (p.created_at > :cursor_val_0)   OR  (p.created_at = :cursor_val_0 AND p.id > :cursor_id_1)ORDER  BY p.created_at ASC, p.id ASC   -- reversedLIMIT  16

HTML Template Example

The following example shows how to render a paginated list with previous/next navigation links using the CursorPaginator in a PHP template:

<?phpuse Doctrine\ORM\Tools\Pagination\CursorPaginator;$cursor = $_GET['cursor'] ?? null;$query = $entityManager->createQuery('SELECT p FROM BlogPost p ORDER BY p.createdAt DESC, p.id DESC');/** @var CursorPaginator<BlogPost> $paginator */$paginator = new CursorPaginator(limit: 15);$page = $paginator->paginate($query, $cursor);?><p><?= $page->getTotalCount() ?> result(s) in total, <?= $page->count() ?> on this page.</p><ul>    <?php foreach ($page as $post): ?>        <li><?= escape($post->getTitle()) ?></li>    <?php endforeach ?></ul><?php if ($page->hasToPaginate()): ?>    <nav>        <?php if ($page->hasPreviousPage()): ?>            <a href="?cursor=<?= escape($page->getPreviousCursorAsString()) ?>">Previous</a>        <?php endif ?>        <?php if ($page->hasNextPage()): ?>            <a href="?cursor=<?= escape($page->getNextCursorAsString()) ?>">Next</a>        <?php endif ?>    </nav><?php endif ?>

Cursor Encoding

A cursor is serialized to a URL-safe string via Cursor::encodeToString() and deserialized back via the static Cursor::fromEncodedString(). The format is a JSON object encoded with URL-safe Base64 (no padding):

{    "p.createdAt": "2024-01-15T10:30:00+00:00",    "p.id": 42,    "_isNext": true}

The _isNext flag distinguishes next-page cursors from previous-page cursors. All other keys are the DQL path expressions (alias.field) of the ORDER BY columns, and their values are the database representations of the pivot row's field values.

If you need a different serialization format (e.g. encryption), build it on top of a Cursor instance: call $cursor->toArray() to get the raw data, apply your own encoding, and reconstruct with new Cursor($parameters, $isNext).

Limitations

Every ORDER BY column must map to an entity field. Raw SQL expressions or computed columns in ORDER BY are not supported.
The query must have at least one ORDER BY item; the paginator throws a LogicException otherwise.

Writing Strategy-Agnostic Code

Both strategies share two interfaces, so code that only lists results can be written once and work with either one:

PaginatorInterface<T, TPosition>: Implemented by OffsetPaginator (a PaginatorInterface<T, Window>) and CursorPaginator (a PaginatorInterface<T, Cursor|string|null>). Its single method is paginate(Query|QueryBuilder $query, mixed $position = null): Page. The native type of $position is mixed because the position type is strategy specific; the TPosition template parameter narrows it for static analysis.
Page<T>: Implemented by WindowPage and CursorPage. It extends Countable and IteratorAggregate and exposes getItems(), getTotalCount(), hasPreviousPage(), hasNextPage() and hasToPaginate().

Navigating to another page is deliberately left out of Page, since the position types differ: WindowPage::getNextWindow() returns a Window, CursorPage::getNextCursor() a Cursor.

<?phpuse Doctrine\ORM\Query;use Doctrine\ORM\Tools\Pagination\PaginatorInterface;function renderTitles(PaginatorInterface $paginator, Query $query, mixed $position): void{    $page = $paginator->paginate($query, $position);    echo $page->getTotalCount() . " result(s), " . count($page) . " on this page\n";    foreach ($page as $post) {        echo $post->getTitle() . "\n";    }}

Maintained

Unmaintained