Cursor pagination limits total count and random page access in Laravel

Direct Answer

Compared with LengthAwarePaginator, Laravel's CursorPaginator removes three critical capabilities required for total-driven UIs:

• Total Record Count: There is no total() method because no COUNT(*) query is executed. You cannot display "1,240 results found."
• Last Page Calculation: Without a total, lastPage() is unavailable. UI elements like "Page 3 of 42" cannot be rendered.
• Random Access: Navigation is restricted to forward and backward traversal via opaque cursor tokens (nextCursor() and previousCursor()). Users cannot jump directly to a specific page number.

The trade-off is performance: cursorPaginate() uses a WHERE clause on the ordering column (e.g., WHERE id > :cursor LIMIT n), avoiding the expensive OFFSET scan that slows down deep pages in traditional pagination.

Ordering Conditions for Skipped or Duplicated Rows

Cursor pagination can produce inconsistent results under two specific conditions:

1. Non-unique Ordering Columns: The cursor uses a strict comparison (> or <). If multiple rows share the same value in the ordering column and the page boundary falls within that group of ties, the remaining rows with that same value are skipped on the next page. To prevent this, always use a unique tiebreaker: orderBy('created_at')->orderBy('id').
2. Mutable Ordering Columns: If the value of the column used for sorting changes between requests, or if new rows are inserted with values that shift the cursor's relative position, rows may appear on multiple pages or disappear entirely. While offset pagination also suffers from drift, cursor pagination makes this more apparent as there is no total count to reconcile against.

Implementation Guidance

Before switching strategies, verify if pagination is the actual bottleneck. Use DB::listen to compare the execution time of a deep page (e.g., page 10,000) using paginate() versus cursorPaginate(). If the UI requires totals, consider these alternatives:

• Keep LengthAwarePaginator and optimize indexes.
• Use cursorPaginate() but display a cached or approximate total.
• Redesign the UI to use "Load More" or infinite scroll.

Note: This behavior applies to Laravel 8.x through 11.x. Ensure the ORDER BY clause matches the parameters encoded in the cursor token. Simple queries are recommended; JOIN, GROUP BY, or DISTINCT can complicate cursor generation.