Pagination

Ten thousand rows in one response serve neither the client nor the worker’s memory. A result can be cut up in two ways, and they are not interchangeable: offsets are convenient, cursors are correct. Below: how they differ on live data, and every method taken apart — what it accepts, what it returns, what it does at the edges.

What pagination is

Serving a result in windows: the client gets one and a way to ask for the next. A window is two numbers — how many rows and where to start — and “where” comes in two kinds, which is where the whole difference lies.

Offset — “skip 40, give me 20”. That is LIMIT/OFFSET, page numbers, nearly every admin panel.

Cursor — “give me 20 after this position”. The position is a column value from the last row served, packed into an opaque token.

Offset or cursor

The difference looks like taste right up to the moment the data changes between two requests:

Page 1:  id 12, 11, 10, 9   ← the user is reading
        ↓ someone inserts a row
Offset:  OFFSET 4 → 9, 8, 7, 6      ← id 9 shown twice
Cursor:  after id 9 → 8, 7, 6, 5    ← nothing repeated, nothing skipped

An insert shifts every offset, so the row on the boundary is shown a second time — and a delete makes one vanish unnoticed. A cursor is anchored to a value, not to a position.

The second difference is cost. OFFSET 100000 makes the database walk and discard a hundred thousand rows; a cursor becomes WHERE id < :value and uses the index.

Which to use where

Method reference

Paginator

Returns a PaginationResult — an envelope of meta and data. All three methods are static.

repo()

public static function repo(
  RepositoryViewInterface $repo,
  int $size,
  int $offset = 0,
  ?string $entityClassName = null,
  ?callable $mapper = null,
): PaginationResult

An offset page from a repository.

$repo is not a table name but an assembled query: conditions, joins and ordering are set before pagination, and it changes none of them.

Paginator::repo(
  PostRepository::instance('p')
      ->where(Qb::eq('p.status', 'published'))
      ->orderBy('p.created_at DESC'),
  size: 20,
);

$size must be positive. Zero or negative is not an empty page but an error:

ValueError: Size must be a positive integer (>= 1), got: 0.

$offset may run past the end — that is not an error. The data comes back empty while total stays honest, which is how the client learns it overshot:

{ "meta": { "offset": 99, "size": 5, "total": 9 }, "data": [] }

$entityClassName replaces the class rows are hydrated into. Useful when one table is served in several shapes — but it replaces only the class, not the set of columns:

Paginator::repo($repo, size: 2, entityClassName: Slim::class);
// Slim declares id and title, but the row also carried views —
// Deprecated: Creation of dynamic property Slim::$views

To narrow the columns, narrow the repository’s select(); $entityClassName is not for that.

$mapper receives the already hydrated object (the repository’s entity, or $entityClassName), not a row array. Its return value takes the item’s place:

$page = Paginator::repo($repo, size: 2, mapper: fn(Post $p) => $p->title);
$page->data;   // ["t1", "t2"]

It runs only on the rows of the page — the rest are never hydrated at all.

Returns a PaginationResult with PaginationMeta (offset, size, total).

array()

public static function array(
  array $items,
  int $size,
  int $offset = 0,
  ?callable $mapper = null,
): PaginationResult

The same for a list already in memory. No connection is opened.

$items is passed whole: total is its length, which is how the method knows the count without a query. The flip side is obvious — the list must already be in memory.

Returns a PaginationResult with the same PaginationMeta as repo() — the shapes match, so the source can change without touching the client.

$page = Paginator::array($rowsFromApi, size: 10, offset: 20);

Useful when the data did not come from a database — an external API, a cache, a file — and still has to be served in the same envelope as everything else.

cursor()

public static function cursor(
  RepositoryViewInterface $repo,
  int $size,
  CursorKey $key,
  ?string $cursor = null,
  ?string $entityClassName = null,
  ?callable $mapper = null,
): PaginationResult

A page “after a position”.

$key defines both the position and the order: the ORDER BY is built from it, and so is the value for the WHERE. Your own orderBy() on the repository is unnecessary here — it is not aligned with “after this position”, and pages start to overlap.

$cursor is what the client handed back from meta.cursorNext or meta.cursorPrev. null means “from the beginning”, and it is the only way to start: a cursor has no page numbers.

$key = new CursorKey('id', Sort::Desc);

$page = Paginator::cursor(
  PostRepository::instance(),
  size: 20,
  key: $key,
  cursor: $request->query('cursor'),
);

return ResponseEntity::ok($page);

Returns a PaginationResult with PaginationMetaCursor (size, cursorPrev, cursorNext). Throws InvalidCursorException when the token is damaged or was issued under a different key.

At the edges the tokens are null, and the buttons follow from that:

// first page
{ "meta": { "size": 4, "cursorPrev": null, "cursorNext": "eyJzIjoi…" }, "data": [ … ] }

// last one
{ "meta": { "size": 5, "cursorPrev": "eyJzIjoi…", "cursorNext": null }, "data": [ … ] }

Wrapper

The same offset walk, with meta described in pages rather than in offsets. That is what a numbered interface needs: the client does not divide total by size or work out its neighbours.

paginator()

public static function paginator(
  RepositoryViewInterface|array $repo,
  int $limit,
  int $page = 1,
  ?string $entityClassName = null,
  ?callable $mapper = null,
): WrapResult

$repo accepts either kind of source, and that is the main difference from Paginator. With an array the method opens no connection — it slices the list in place:

Wrapper::paginator(range(1, 25), limit: 10, page: 2);
// {"meta":{"current":2,"size":10,"total":25,"pages":3,"previous":1,"next":3},
//  "data":[11,12,13,14,15,16,17,18,19,20]}

$page counts from one, not from zero: page: 1 is the first page, offset 0. Internally it is computed as limit × (page − 1).

$entityClassName is silently ignored for an array — there is nothing to hydrate, and the items are served as they are:

Wrapper::paginator([1, 2, 3, 4], limit: 2, entityClassName: Slim::class);
// data: [1, 2] — no hydration happened

Returns a WrapResult with WrapMeta. The meta is computed like this:

$result = Wrapper::paginator(PostRepository::instance(), limit: 5, page: 2);

{
"meta": {
  "current": 2,      // the page that was asked for
  "size": 5,         // the page size
  "total": 12,       // all rows — that same COUNT
  "pages": 3,        // ceil(total / size)
  "previous": 1,     // current − 1, or null on the first
  "next": 3          // current + 1 when pages > current, else null
},
"data": [ { "id": 6 }, { "id": 7 }, { "id": 8 }, { "id": 9 }, { "id": 10 } ]
}

previous and next are null at the edges, so the client draws its arrows straight from them, with no arithmetic of its own.

{"meta":{"current":9,"size":5,"total":9,"pages":2,"previous":8,"next":null},"data":[]}

An empty source gives pages: 0 rather than one empty page — there really are no pages:

{"meta":{"current":1,"size":5,"total":0,"pages":0,"previous":null,"next":null},"data":[]}

CursorKey

Describes a position: which column to walk, in which direction, and how to break a tie.

new CursorKey(
  string $column,
  Sort $direction = Sort::Desc,
  ?CursorKey $tiebreaker = null,
  ?string $alias = null,
)

$column is what gets compared. It must be part of the selection: its value is what goes into the token. With joins, write it with the table alias (p.created_at).

$direction sets both the order and the meaning of the comparison: Desc is “rows below the value”, Asc is “above”. Moving backwards inverts it automatically.

$alias is for when the SELECT renames the column (p.created_at AS posted_at): the comparison must use p.created_at, while the value is read from posted_at.

The tiebreaker, and why it is mandatory

A cursor means “rows after this value”. If more rows share a value than fit on a page, the boundary stops being unambiguous: the database may return them in any order, and some will be lost or repeated.

// bad: a hundred posts share the same created_at
$key = new CursorKey('created_at', Sort::Desc);

// good: the tie is broken by a unique key
$key = new CursorKey('created_at', Sort::Desc,
  tiebreaker: new CursorKey('id', Sort::Desc));

With a chain both values go into the token, and the comparison is made on the pair:

key: views DESC, then id DESC
page 1 → (id 8, views 2), (id 5, views 2), (id 2, views 2)
token  → {"s":"bfe46c74","v":[2,2],"d":"f"}
page 2 → (id 7, views 1), (id 4, views 1), (id 1, views 1)

The rule: the last key in the chain must be unique. Usually that is the primary key.

Utility methods

signature() is what binds a token to its key: it sits inside the cursor and is checked on the way back. Change the ordering and old tokens stop fitting — they fail instead of producing a page assembled under a different order.

Response shapes

Every envelope implements JsonSerializable, so a controller returns them as they are.

PaginationResult

PaginationMeta

Returned by repo() and array().

size is the request, not the fact: the last page comes back shorter. Count what arrived with count($result->data).

PaginationMetaCursor

Returned by cursor(). There is no total here — a cursor does not compute it, and that is where the saving comes from.

WrapMeta

Returned by Wrapper::paginator().

What is inside a cursor

The token is opaque to the client but not encrypted — it is base64 of a small JSON:

eyJzIjoiZGI4MTRhYWMiLCJ2IjpbOV0sImQiOiJmIn0=
  ↓ base64_decode
{"s":"db814aac","v":[9],"d":"f"}
 s — the key's signature   v — position values   d — direction (f forward, b backward)

Errors

The second case is ordinary life: a link was copied incompletely. The third means you changed the ordering while a client still holds an old token.

try {
  $page = Paginator::cursor($repo, size: 20, key: $key, cursor: $request->query('cursor'));
} catch (InvalidCursorException) {
  // not our token, or a damaged one — show the first page
  $page = Paginator::cursor($repo, size: 20, key: $key);
}

The dedicated exception is not pedantry: starting over silently means showing the user the first page where they expected a continuation, and leaving nothing in the logs. The package reports the problem; whether to restart or answer 400 is the application’s decision.

Next

• Repositories — building the query you are paginating
• Connection pool — why a second query for COUNT is worth noticing
• PPA — the layer as a whole