skip to content

In Laravel, what is the difference between paginate(), simplePaginate() and cursorPaginate(), and which SQL queries does each run?

level: juniorimportance: must knowfreq 60%

answer

  1. three paginator classes
  2. COUNT query plus LIMIT/OFFSET
  3. LIMIT perPage + 1 to detect more
  4. WHERE on the ordered columns
  5. 15 per page, ?page= and ?cursor=

basics

~20 s

paginate() runs a COUNT query plus a LIMIT/OFFSET query and returns a LengthAwarePaginator with totals and page numbers; simplePaginate() runs one LIMIT/OFFSET query for next/previous only; cursorPaginate() filters on the ordered columns instead of using an offset.

solid answer

~40 s

`paginate()` first runs a `COUNT` of the query, then fetches one page with `LIMIT perPage OFFSET (page - 1) * perPage`, and returns a `LengthAwarePaginator` that knows `total()` and `lastPage()` and can render numbered links. `simplePaginate()` skips the count and fetches `perPage + 1` rows at the same offset; the extra row only tells it whether a next page exists, and it returns a `Paginator` with previous/next links. `cursorPaginate()` returns a `CursorPaginator`: instead of an offset it adds a `WHERE` comparing the ordered columns with the values encoded in the `cursor` query parameter, also fetching `perPage + 1`. The page comes from `?page=`; Eloquent defaults to the model's `$perPage` of 15, and the query builder to 15.

code

php · 16 lines
php
<?php

use App\Models\Activity;

// COUNT(*) + LIMIT 20 OFFSET n -> LengthAwarePaginator
$page = Activity::latest()->paginate(20);
$page->total();
$page->lastPage();

// LIMIT 21 OFFSET n -> Paginator (next/previous only)
$simple = Activity::latest()->simplePaginate(20);
$simple->hasMorePages();

// WHERE on order columns + LIMIT 21 -> CursorPaginator
$feed = Activity::latest()->orderByDesc('id')->cursorPaginate(20);
$feed->nextCursor()?->encode();

go deeper

for a junior

Recall the three methods, the classes they return, the default 15 per page and the ?page= parameter.

for a middle

Explain the SQL each one runs: the COUNT query, the perPage + 1 trick, and the WHERE clause a cursor produces.

for a senior

Pick the method from the interface and the table's growth, and justify it with the cost of the count query and large offsets.

for a principal

Set a team default for list endpoints, weighing numbered navigation against stable, cheap feeds as tables grow.

## Three methods, three classes Laravel's query builder and Eloquent builder both offer three pagination methods. Each returns a different **paginator** class, and each sends different SQL to the database. | Method | Returns | Queries | Knows the total? | Links | |---|---|---|---|---| | `paginate()` | `LengthAwarePaginator` | `COUNT` + `LIMIT`/`OFFSET` | yes | numbered pages | | `simplePaginate()` | `Paginator` | one `LIMIT perPage + 1` / `OFFSET` | no | previous / next | | `cursorPaginate()` | `CursorPaginator` | one `WHERE` on order columns, `LIMIT perPage + 1` | no | previous / next | All three read the current position from the request automatically: `?page=` for the offset paginators (an invalid or missing value falls back to 1) and `?cursor=` for the cursor paginator. The per-page size is the first argument; if omitted, Eloquent uses the model's `$perPage` property (15 by default) and the query builder uses 15. ## paginate(): count, then fetch For the activity feed of a social running app, `Activity::where('user_id', $id)->latest()->paginate(20)` runs: 1. a count query built from the same constraints, with `ORDER BY`, `LIMIT` and `OFFSET` removed (queries with `GROUP BY` or `HAVING` are wrapped in a subquery and counted as a whole); 2. the page query, `... ORDER BY created_at DESC LIMIT 20 OFFSET 40` for page 3. The paginator computes `lastPage()` as the total divided by the page size, rounded up (at least 1). A page number beyond the last page is not an error: the page query returns no rows and the paginator is simply empty. When the count is zero, the page query is skipped. ## simplePaginate(): one query, one extra row `simplePaginate(20)` runs a single query with `LIMIT 21 OFFSET 40`. If 21 rows come back, there is a next page; the 21st row is dropped before you see the items. The result has `hasMorePages()`, `previousPageUrl()` and `nextPageUrl()`, but no `total()` or `lastPage()`, so its default view renders only previous and next links. It is the cheap choice whenever the interface never shows "page 3 of 57". ## cursorPaginate(): no offset at all `cursorPaginate(20)` needs an `ORDER BY`. On the first request it runs `... ORDER BY created_at DESC, id DESC LIMIT 21`. The **cursor** for the next page is the ordered column values of the last item, JSON-encoded and base64url-encoded into `?cursor=`. The next request turns that back into a condition such as: - `WHERE (created_at < ? OR (created_at = ? AND id < ?))` - plus the same `ORDER BY` and `LIMIT 21`. Because the database seeks straight to the position, the cost does not grow with the page number the way a large `OFFSET` does, and rows inserted at the top of the feed do not shift the next page. The price: no totals, no jump to page N, and strict ordering rules (unique, non-null order columns that belong to the paginated table). ## Paginating data you already have The same three classes can be built by hand when the items are already in memory, for example a list assembled from an external API: - `new LengthAwarePaginator($items, $total, $perPage, $currentPage, $options)` for numbered pages; - `new Paginator($items, $perPage, $currentPage, $options)` for previous/next only; - `new CursorPaginator($items, $perPage, $cursor, $options)` for cursor-style navigation. Unlike the query methods, these constructors do not slice anything for you: you pass only the current page's items (for the simple paginators, one extra item signals a next page). Passing `['path' => $request->url()]` in the options makes the generated links point at the current route. ## Choosing - Numbered pages and a total in an admin table: `paginate()`. - "Newer / older" links on a large table where the total is never shown: `simplePaginate()`. - Infinite scroll or an API feed over a table that grows constantly: `cursorPaginate()`. ## Things interviewers listen for - Naming the extra `COUNT` query, and that it is what makes `paginate()` expensive on big filtered tables. - Knowing that `simplePaginate()` and `cursorPaginate()` fetch one extra row to decide whether a next page exists. - Knowing that `?page=` values are validated and fall back to 1, and that an out-of-range page returns an empty result rather than a 404.

  • How can paginate() avoid the COUNT query when you already know the total?
    `paginate()` takes a fifth argument, `$total`, as a value or a closure. When it is given, Laravel uses it instead of running the count query, for example a cached count of a user's activities. The paginator still offers `total()` and numbered links from that value.
  • What happens when a user requests ?page=500 on a paginate() result with only 3 pages?
    Nothing fails. The page query runs with a large offset and returns no rows, so the paginator is empty while `total()` and `lastPage()` still report the real values. If you want a redirect or 404, check `$paginator->currentPage() > $paginator->lastPage()` yourself.

saying these in an interview costs you the question

  • simplePaginate() still runs a COUNT query, just hides the total
  • cursorPaginate() uses OFFSET with an encoded page number
  • paginate() loads every row and slices the page in PHP
  • An out-of-range ?page= throws a 404 automatically
  • cursorPaginate() can render numbered page links