Offset pagination on a list ordered by recency shows an item twice when a row is inserted between requests, and skips one when a row is deleted.
$query->orderBy('placed_at', 'desc')->orderBy('id', 'desc');
if ($cursor) {
$query->where(function ($q) use ($cursor) {
$q->where('placed_at', '<', $cursor['placed_at'])
->orWhere(function ($q) use ($cursor) {
$q->where('placed_at', '=', $cursor['placed_at'])
->where('id', '<', $cursor['id']);
});
});
}
The tiebreaker on the primary key is what makes it correct rather than nearly correct, because two rows sharing a timestamp are common and a cursor on a non-unique column skips or repeats at exactly that boundary. Opaque cursors stop a client reconstructing one and coupling itself to the sort order. Jumping to page seven becomes impossible, which matters for an admin table and not for an infinite scroll — so the choice is per endpoint.