A Laravel activity feed uses cursorPaginate() ordered only by created_at — why can it skip or repeat activities, and what ordering rules does cursor pagination need?
answer
- cursor = last row's order values
- ties on created_at break the seek
- add orderByDesc('id') as tiebreaker
- no NULLs; columns of this table
- expressions aliased and selected
basics
~20 sThe cursor stores the last row's created_at and the next page asks for rows strictly older, so rows sharing that timestamp are skipped. Order by a unique combination, such as created_at then id, with non-null columns from the paginated table.
solid answer
~40 s`cursorPaginate()` encodes the ordered column values of the last item into the cursor and turns them into a strict comparison for the next page: `WHERE created_at < ?` for a descending feed. If several activities share the boundary timestamp, those not already shown are skipped. The rule is that the ordering must be **unique**: add a tie-breaker, `->latest()->orderByDesc('id')`, and Laravel builds `created_at < ? OR (created_at = ? AND id < ?)`. The ordered columns must not contain `NULL`s and must belong to the paginated table; an ordering expression must be aliased and selected, and expressions with bindings are not supported. With no `orderBy`, Eloquent adds the primary key ascending, while `DB::table()->cursorPaginate()` throws a `RuntimeException`. Index the order columns in the same sequence so each page is a seek.
code
php · 12 lines<?php
use App\Models\Activity;
// Unsafe: ties on created_at are skipped at page boundaries
Activity::latest()->cursorPaginate(20);
// Safe: unique ordering, created_at then id, both descending
// WHERE (created_at < ? OR (created_at = ? AND id < ?))
Activity::latest()
->orderByDesc('id')
->cursorPaginate(20);go deeper
Recall that cursorPaginate() needs an orderBy and that the order should end in a unique column such as id.
Explain how the cursor becomes a WHERE clause over the order columns and why ties or NULLs make rows disappear.
Diagnose skipped or repeated feed items from the ordering, add a tie-breaker and a matching composite index, and handle stale cursors.
Set rules for which list endpoints may offer user-selected sorts under cursor pagination, since every sort needs a unique, indexed ordering.
## What a cursor really is `cursorPaginate()` does not remember a page number. After fetching `perPage + 1` rows, it takes the **ordered column values of the last row on the page** and encodes them, with a direction flag, as JSON and then base64url into `?cursor=`. The next request decodes it and adds a condition built from the query's own `ORDER BY` columns: - for an ascending column: `column > cursor_value`; - for a descending column: `column < cursor_value`; - for several columns, a nested expansion: `(a < ?) OR (a = ? AND b < ?)`, and so on for each further column. Going back with `prev_cursor` flips every direction, fetches, and re-reverses the page so items stay in display order. ## Why created_at alone fails The social running app's feed runs `Activity::latest()->cursorPaginate(20)`. Imagine a club import that wrote 30 activities with the same `created_at` second, and a page boundary falls inside that group: 1. Page 1 ends on an activity stamped `2026-09-29 07:00:00`; the cursor stores that timestamp. 2. Page 2 asks for `created_at < '2026-09-29 07:00:00'`. 3. Every activity with exactly that timestamp that did not fit on page 1 is **skipped** for good. If the order is not deterministic at all, the database may also return tied rows in a different order on each request, so users see **repeats** and gaps. The cursor is only as precise as the ordering. ## The ordering rules Laravel's docs and source set these requirements: | Rule | Why | |---|---| | Order by a unique column or unique combination | ties at the boundary would be skipped | | Order columns contain no `NULL`s | comparisons never match `NULL` rows, and a `NULL` boundary becomes a null check, so rows vanish or repeat | | Order columns belong to the paginated table | the cursor value is read from the result item | | An ordering expression is aliased and selected | the cursor needs a value to store for it | | No expressions with bindings in the order | not supported by the cursor builder | The fix for the feed is one extra call: `Activity::latest()->orderByDesc('id')->cursorPaginate(20)`. The primary key makes the combination unique, and Laravel generates `created_at < ? OR (created_at = ? AND id < ?)`. ## Diagnosing it The symptom report is usually vague: "some runs never show up in the feed" or "I saw the same run twice". To confirm: 1. Log the SQL of two consecutive requests and read the cursor condition; if it compares only `created_at`, ties are the suspect. 2. Count rows per timestamp in the affected range; bulk imports, seeders and batch jobs are the usual sources of identical values. 3. Decode the `cursor` value (base64url, then JSON) to see the exact boundary the client sent. 4. Add the tie-breaker and re-run the two requests: the second page should now start exactly after the last row of the first. ## Missing orderBy - On an **Eloquent** query with no `orderBy`, Laravel adds the model's primary key, ascending, before paginating. - On a **query builder** query (`DB::table('activities')`), there is no model to ask, so it throws `RuntimeException` with "You must specify an orderBy clause when using this function." ## Performance A cursor query is only fast if the database can seek straight to the cursor position. Create a composite index matching the order, such as `(created_at, id)`, or, for a per-user feed filtered by `user_id`, `(user_id, created_at, id)`. Without it, the database still sorts the candidate rows on every request. ## Cursors are not secrets The cursor is plain base64url-encoded JSON, not signed or encrypted. A client can decode it and see the boundary values. Tampering only changes the bound values in the `WHERE` clause, so it cannot inject SQL, but do not put anything confidential in the order columns. If the sort changes between requests, for example a client switches the feed from newest to longest run while holding an old cursor, the cursor lacks the new order column and Laravel throws an `UnexpectedValueException`; handle that when sorting is user-controlled.
- What happens if a feed is cursor-paginated by a nullable finished_at column?Rows whose `finished_at` is `NULL` never satisfy the `<` or `>` comparison the cursor adds, so they drop out. If the boundary row itself has `NULL`, the query builder turns `where(col, '<', null)` into a not-null check, so the next page restarts and repeats rows. Laravel's docs list null-valued order columns as unsupported.
- What does Laravel do when a client sends a cursor produced under a different sort order?The decoded cursor lacks a value for the new order column, so `Cursor::parameter()` throws an `UnexpectedValueException`, which becomes a server error unless handled. When the sort is user-selectable, catch it or validate that the cursor matches the current sort.
A cursor is a bookmark that records the last line you read, not the page number. If several lines are identical, a bookmark that stores only the line's text cannot tell you which copy you stopped at, so you need a line number beside it.
saying these in an interview costs you the question
- Ordering by created_at alone is unique enough for a cursor
- The cursor is encrypted, so clients cannot read it
- cursorPaginate() on DB::table() without orderBy sorts by id
- A tampered cursor lets the client inject SQL
- Nullable order columns work because NULLs sort last