CursorPaginationHelper paginates a Doctrine QueryBuilder using a position cursor rather than an offset. Each page response includes an opaque cursor that the client passes back to fetch the next or previous page. Because no OFFSET clause is used, performance stays constant regardless of how deep into the dataset the client has scrolled.
For HTML views with page-number navigation use Offset Pagination instead.
Offset pagination (LIMIT 20 OFFSET 200) tells the database to skip 200 rows before returning 20. On a large table this forces a full scan of the skipped rows every time. At page 500 it is noticeably slow; at page 5000 it is unacceptable.
Cursor pagination encodes the position of the last seen item and issues a WHERE clause instead of an OFFSET. The database jumps directly to that position using an index. Page 5000 costs exactly the same as page 1.
The trade-off: clients can only navigate forward and backward from their current position — they cannot jump to an arbitrary page number.
use PHP_SF\System\Classes\Helpers\CursorPaginationHelper;
use PHP_SF\System\Classes\Helpers\PaginationCursor;
#[Route( url: 'api/players', httpMethod: 'GET', middleware: auth::class )]
public function list_players(): ApiResponse
{
$qb = em( 'postgresql' )->createQueryBuilder()
->select( 'e' )
->from( Player::class, 'e' )
->andWhere( 'e.active = true' );
$result = CursorPaginationHelper::paginate(
qb: $qb,
sortField: 'createdAt',
cursor: PaginationCursor::tryFromString( $this->request->query->get( 'cursor' ) ),
perPage: (int) $this->request->query->get( 'per_page', 20 ),
);
return $this->apiSuccess( data: $result->items, pagination: $result );
}
PaginationCursor is a value object that wraps the opaque cursor string. It validates and decodes the cursor at construction time so the rest of the code works with typed properties instead of raw strings and array key lookups.
// Null-safe — passes null through, throws InvalidArgumentException on invalid non-null string
$cursor = PaginationCursor::tryFromString( $this->request->query->get( 'cursor' ) );
// Strict — always throws InvalidArgumentException on invalid input
$cursor = PaginationCursor::fromString( $rawString );
Both throw InvalidArgumentException when given a malformed value. tryFromString simply passes null through to represent "first page" — it does not suppress errors on invalid strings.
final class PaginationCursor
{
public readonly mixed $field; // decoded sort-field value
public readonly int $id; // entity id at the cursor position
public readonly bool $isForward; // true = next page, false = previous page
}
The helper creates these automatically — you will not normally call these directly. They are useful in tests and custom pagination logic:
$next = PaginationCursor::after( $entity, 'createdAt' ); // forward cursor
$prev = PaginationCursor::before( $entity, 'createdAt' ); // backward cursor
$cursorString = $cursor->toString();
// or
$cursorString = (string) $cursor;
CursorPaginationResult::getPaginationMeta() calls toString() automatically when building the JSON response — controllers never need to serialize cursors manually.
CursorPaginationHelper::paginate(
QueryBuilder $qb,
string $sortField,
string $entityAlias = 'e',
?PaginationCursor $cursor = null,
int $perPage = 20, // capped at 100
): CursorPaginationResult
| Parameter | Default | Description |
|---|---|---|
$qb |
— | Doctrine QueryBuilder with filters/joins applied |
$sortField |
— | Entity property name used for ordering (must be non-nullable) |
$entityAlias |
'e' |
DQL alias for the root entity in the QB |
$cursor |
null |
Parsed PaginationCursor; null = first page |
$perPage |
20 |
Items per page — capped at 100 |
Rules for the QueryBuilder:
->select('e')WHERE conditions and joins before calling paginate()->orderBy() or ->setMaxResults() — the helper owns those clauses$sortField must correspond to a non-nullable column for stable orderingpaginate() returns a final readonly value object:
final readonly class CursorPaginationResult
{
public array $items; // hydrated entities for this page
public ?PaginationCursor $cursor; // cursor used for this request (null on first page)
public ?PaginationCursor $nextCursor; // cursor for the next page, null if no more results
public ?PaginationCursor $prevCursor; // cursor for the previous page, null on first page
public int $perPage; // effective items per page
public bool $hasMore; // true if a next page exists
}
Cursors are stored as PaginationCursor objects — access typed properties (->id, ->field, ->isForward) without decoding. getPaginationMeta() serializes them to strings for the JSON envelope automatically.
Pass $result->items as data and the full $result as pagination to apiSuccess():
return $this->apiSuccess( data: $result->items, pagination: $result );
The response body becomes:
{
"success": true,
"data": [ ... ],
"errors": null,
"meta": {
"timestamp": 1746316800,
"pagination": {
"cursor": "eyJmaWVsZCI6MTc0NjMwMDAwMCwiaWQiOjEsImRpciI6Im5leHQifQ==",
"next_cursor": "eyJmaWVsZCI6MTc0NjMxNjgwMCwiaWQiOjIxLCJkaXIiOiJuZXh0In0=",
"prev_cursor": null,
"per_page": 20,
"has_more": true
}
}
}
# First page — no cursor
GET /api/players
# Next page — pass next_cursor from the previous response
GET /api/players?cursor=eyJmaWVsZCI6MTc0...
# Previous page — pass prev_cursor from the current response
GET /api/players?cursor=eyJmaWVsZCI6MTc0...
Rules for clients:
prev_cursor is null on the first pagenext_cursor is null when has_more is falseper_page from the response to know how many items were returnedCursorPaginationHelper::DEFAULT_PER_PAGE // 20
CursorPaginationHelper::MAX_PER_PAGE // 100
$perPage values below 1 are clamped to 1; values above 100 are clamped to 100.
The cursor encodes the sort-field value and the id of the last seen item as base64(json_encode([...])):
{ "field": 1746316800, "id": 21, "dir": "next" }
On the next request, the helper adds a WHERE clause that positions the query after (or before) that item:
-- forward
WHERE (e.createdAt > :cursor_field)
OR (e.createdAt = :cursor_field AND e.id > :cursor_id)
ORDER BY e.createdAt ASC, e.id ASC
-- backward (prev_cursor)
WHERE (e.createdAt < :cursor_field)
OR (e.createdAt = :cursor_field AND e.id < :cursor_id)
ORDER BY e.createdAt DESC, e.id DESC
The (field, id) compound sort guarantees stability when multiple rows share the same sortField value.
DateTimeInterface values are serialised to a Unix timestamp in the cursor, so they survive JSON encoding correctly.
All filtering happens on the QueryBuilder before calling paginate(). The helper only adds cursor position constraints on top:
$qb = em( 'postgresql' )->createQueryBuilder()
->select( 'e' )
->from( Player::class, 'e' )
->andWhere( 'e.guild = :guild' )
->andWhere( 'e.level >= :minLevel' )
->setParameter( 'guild', $guildId )
->setParameter( 'minLevel', 10 );
$result = CursorPaginationHelper::paginate(
qb: $qb,
sortField: 'level',
cursor: PaginationCursor::tryFromString( $this->request->query->get( 'cursor' ) ),
perPage: 50,
);
If the root entity alias in your QueryBuilder is not 'e', pass it explicitly:
$qb = em( 'mysql' )->createQueryBuilder()
->select( 'post' )
->from( Post::class, 'post' )
->leftJoin( 'post.tags', 'tag' )
->andWhere( 'tag.name = :tag' )
->setParameter( 'tag', 'featured' );
$result = CursorPaginationHelper::paginate(
qb: $qb,
sortField: 'publishedAt',
entityAlias: 'post',
cursor: PaginationCursor::tryFromString( $this->request->query->get( 'cursor' ) ),
);
PaginationHelper |
CursorPaginationHelper |
|
|---|---|---|
| Input | Array or collection | Doctrine QueryBuilder |
| Mechanism | Array slice / OFFSET |
WHERE (field, id) > cursor |
| Performance on deep pages | Degrades | Constant |
| Arbitrary page jump | Yes | No |
| Total count available | Yes (getTotalPages()) |
No |
| Suited for | HTML views | API endpoints |
| Direction | Page number | Next / previous cursor |