Pagination

Ten thousand rows in one response fit neither the client nor the worker’s memory. There are two ways to slice a result set, and they are not interchangeable: offset is convenient, cursor is correct. Below: how they differ on live data, and every method in detail — what it takes, what it returns, what it does at the edges.

What pagination is and why

The problem. The list grows while the query for it stays the same. With a hundred rows in the table, SELECT * looks harmless; at ten thousand it eats the worker’s memory, and at a million it takes the process down. The client does not need all of it anyway — it is showing one screen.

The solution. Hand out a window and a way to ask for the next one. A window is defined by two numbers — how many rows and where to start — and it is that “where” that 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 the value of a column on the last row handed out, wrapped in an opaque token.

Offset or cursor

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

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

An insert shifts every offset, so the row on the boundary is shown a second time; a delete makes one disappear unnoticed. A cursor is tied to a value, not to a number.

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

The practical rule: take page numbers when the user can see them. If the interface has only a “load more” button, there is nothing to pay a COUNT for — and if the list is live, a cursor also removes the repeats at the boundaries.

Reference

Paginator

Paginator is a set of three static methods, each of which assembles one page and returns a PaginationResult: an envelope of meta (the window’s description) and data (the rows).

The class holds nothing between calls and needs no constructing: a page is described entirely by the arguments. The methods differ in their source and in how the position is given — repo() takes an offset against the database, array() against a list already in hand, and cursor() walks by position instead of offset.

use Flytachi\Winter\Ppa\Pagination\Paginator;

$page = Paginator::repo(PostRepository::instance(), size: 20);

return ResponseEntity::ok($page);

What the client receives

{
"meta": { "offset": 0, "size": 20, "total": 137 },
"data": [ { "id": 137, "title": "…" }, … ]
}

repo()

Assembles a page by offset from a repository.

The most direct way to slice a result set: the method appends LIMIT and OFFSET to your query, runs it, and with a second query counts how many rows satisfy the conditions in total. That second number is what lets the client draw “page 3 of 12” — and it is also what you pay an extra round trip for.

Syntax

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

Parameters

$repo — not a table name but an assembled query. Conditions, joins and ordering go on the repository before the call, and pagination does not touch them — it only appends the window.

$size — how many rows to return. A positive number only; zero or negative is not an empty page but an error.

$offset — how many rows to skip from the start. 0 by default. A value past the end of the result set is not an error: the data comes back empty and total stays honest.

$entityClassName — the class rows are hydrated into instead of the repository’s entity. null by default, meaning the repository’s entity is used. Only the class is substituted: the set of columns stays as it was.

$mapper — a function every row of the page is passed through. null by default, meaning rows are handed over as they are. It receives an already hydrated object, not an array, and its return value takes the element’s place.

Returns

A PaginationResult whose meta is PaginationMeta: offset, size, total.

Errors

ValueError — if $size is below one:

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

Example

// Conditions and ordering belong to the repository; pagination only slices
Paginator::repo(
  PostRepository::instance('p')
      ->where(Qb::eq('p.status', 'published'))
      ->orderBy('p.created_at DESC'),
  size: 20,
);

An offset past the end

Paginator::array(range(1, 9), size: 5, offset: 99);

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

total is how the client works out that it overshot: there are nine rows in all, and it asked to skip ninety-nine.

Transforming rows

$page = Paginator::repo($repo, size: 2, mapper: fn(Post $post) => $post->title);
$page->data;   // ["The first title", "The second title"]

The function is called only on the rows of the page — the rest are never hydrated at all.

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

array()

Assembles a page by offset from a list already in hand.

The same thing as repo(), but the source is already in memory: no connection is opened and no COUNT is run. Useful when the data did not come from the database — from an external API, from a cache, from a file — and still has to go out in the same envelope as everything else, so the client cannot tell the sources apart.

Syntax

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

Parameters

$items — the source list in full. Its length becomes total, which is how the total is known without any queries; the flip side is obvious — the list must already be in memory.

$size — how many elements to return. As in repo(), a positive number only.

$offset — how many elements to skip. 0 by default.

$mapper — a function transforming the elements of the page. null by default. Unlike repo(), it receives the list element as it is — there is nothing to hydrate here.

Returns

A PaginationResult with the same PaginationMeta as repo() — the shapes match, so the source can be swapped without touching the client.

Errors

ValueError — if $size is below one.

Example

$page = Paginator::array(range(1, 4), size: 2, mapper: fn(int $n) => $n * 10);

Result

{ "meta": { "offset": 0, "size": 2, "total": 4 }, "data": [10, 20] }

cursor()

Assembles the page “after a position”.

Instead of a row number the method receives a token holding the value of the sorting column on the last row handed out. That value builds the WHERE, and the key’s description builds the ORDER BY. Neither OFFSET nor COUNT is run: hence both the stability under inserts and the constant per-page cost regardless of depth.

The price is the absence of numbers: you cannot jump straight to page seven, only forward and back.

Syntax

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

Parameters

$repo — a repository with its conditions already applied, as in repo().

$size — how many rows to return; a positive number only.

$key — a CursorKey: which column to walk and in which direction. It sets 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 not needed here: it does not agree with the “after this position” condition, and the pages start overlapping.

$cursor — the token the client returned from meta.cursorNext or meta.cursorPrev. null by default, meaning “from the beginning”, and that is the only way to start: a cursor has no page numbers.

$entityClassName — the class to hydrate into, as in repo().

$mapper — the row transformation, as in repo().

Returns

A PaginationResult whose meta is PaginationMetaCursor: size, cursorPrev, cursorNext. There is no total in it — a cursor never computes one, and that is where the saving comes from.

Errors

InvalidCursorException — if the token is damaged or was issued under a different sort key.

Example

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

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

return ResponseEntity::ok($page);

Result

At the edges the tokens are null — that is what the buttons are drawn from:

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

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

Wrapper

Wrapper is a second envelope over the same offset walk, whose meta is described in pages rather than in offsets.

The difference is not in the queries but in what the client receives: instead of offset and total it gets the current page number, how many pages there are, and the numbers of the neighbours. For a numbered interface that removes the arithmetic — no dividing total by size and no checking the edges.

The second difference is the source: Wrapper accepts both a repository and a plain array, where Paginator has two separate methods for that.

Wrapper::paginator()

Assembles a page by its number.

Syntax

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

Parameters

$repo — a repository or a list in memory. This is the main difference from Paginator: with an array the method opens no connection and slices the list in place.

$limit — the page size. A positive number only; zero or negative raises a ValueError.

$page — the page number, counting from one. 1 by default. The offset is computed internally as limit × (page − 1), so page: 1 means offset 0.

$entityClassName — the class to hydrate rows into. null by default. For an array it is ignored silently: there is nothing to hydrate, and the elements go out as they are.

$mapper — the transformation applied to the page’s rows. null by default.

Returns

A WrapResult whose meta is WrapMeta: current, size, total, pages, previous, next.

Errors

ValueError — if $limit is below one.

Example

Wrapper::paginator(range(1, 25), limit: 10, page: 2);

Result

{
"meta": {
  "current": 2,      // the requested page
  "size": 10,        // the page size
  "total": 25,       // rows in all — that same COUNT
  "pages": 3,        // ceil(total / size)
  "previous": 1,     // current − 1, or null on the first
  "next": 3          // current + 1 if pages > current, otherwise null
},
"data": [11, 12, 13, 14, 15, 16, 17, 18, 19, 20]
}

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

An empty source

There really are no pages — pages: 0, not one empty page:

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

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

CursorKey

CursorKey describes the position for cursor(): which column to walk, in which direction, and what breaks a tie.

One object is responsible for three things at once. The query’s ORDER BY is built from it; so is the decision of which value to put into the token; and its shape is signed, so that a token issued under one ordering cannot be applied to another.

Syntax

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

Parameters

$column — the column that sets the order. It goes into both the ORDER BY and the WHERE, so it must be in the selection: the token’s value is taken from it. For queries with joins, write it with the table alias — p.created_at.

$direction — Sort::Asc or Sort::Desc; Sort::Desc by default. It sets not only the order but the meaning of the comparison: Desc means “rows below the value”, Asc means “above”. When walking backwards the direction is inverted automatically.

$tiebreaker — the next key, applied when the first one’s values are equal. null by default. Below is why it almost always has to be set.

$alias — the name the column arrives under in the result. null by default, meaning the same as $column. Needed 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.

Example

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

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 is free to 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 runs 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 ties a token to a key: it sits inside the cursor and is checked on read. Change the ordering and the old tokens stop matching, producing an error instead of a page assembled under a different order.

Response shapes

Every envelope implements JsonSerializable, so they are returned from a controller as they are — no converting to an array by hand.

PaginationResult

The envelope all three Paginator methods return.

PaginationMeta

The meta of a window defined by an offset. Returned by repo() and array().

size is a request, not a fact: on the last page fewer rows arrive. Count what arrived with count($result->data).

PaginationMetaCursor

The meta of a window defined by a position. Returned by cursor().

There is no total here: a cursor does not compute one, and that is exactly what saves the second query.

WrapMeta

The meta described in pages. 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 — the position values   d — direction (f forward, b back)

Errors

ValueError

The page size is below one. Thrown by all four methods — repo(), array(), cursor() and Wrapper::paginator().

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

Zero is treated as an error rather than an empty page deliberately: the page size almost always arrives from a client request, and ?size=0 is either a typo or somebody probing. An empty response to it would look like a normal result.

InvalidCursorException

The token cannot be applied. Thrown by cursor() in two cases.

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

How to handle it

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

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

Next

• Repositories — how to assemble the selection you are paginating
• Entities — the objects a page’s rows are hydrated into
• Connection pool — why the second query for COUNT deserves attention
• PPA — the layer as a whole