Pagination and Filtering
paginate counts rows, simplePaginate does not, cursorPaginate uses a where clause, and a sort field needs an allow-list.
Post::query()->latest('id')->paginate(15)->withQueryString()An index endpoint that returns every row works until the table grows. Three query builder methods paginate it, and they differ in what they cost and what they can tell the client.
The three paginators
| Method | Returns | Cost | Can report |
|---|---|---|---|
paginate(15) | LengthAwarePaginator | Two queries: a count and a page | total, last_page, page numbers |
simplePaginate(15) | Paginator | One query | Next and previous only |
cursorPaginate(15) | CursorPaginator | One query, no offset | Next and previous only |
paginate and simplePaginate use SQL offset, which the database reaches by scanning the rows it then discards. cursorPaginate builds a where clause from the ordered columns instead, so it stays fast on large tables and never skips or repeats a row when records are inserted while a client is paging. It needs an order by on a unique column and cannot produce page numbers.
meta and links
Pass a paginator through a resource collection and the response gains two keys beside data: links (first, last, prev, next) and meta (current_page, from, last_page, path, per_page, to, total). Return the paginator directly instead and you get a flatter, different shape with next_page_url at the top level. Choose one and keep it.
Filtering
Validate query parameters with the rules you would use on a form. The sort field is the one that bites: a column name in orderBy() is written into the SQL, not bound as a parameter, so it has to be checked against an allow-list.
Example
<?php
// app/Http/Controllers/Api/PostController.php
declare(strict_types=1);
namespace App\Http\Controllers\Api;
use App\Http\Controllers\Controller;
use App\Http\Resources\PostResource;
use App\Models\Post;
use Illuminate\Http\Request;
use Illuminate\Http\Resources\Json\AnonymousResourceCollection;
use Illuminate\Validation\Rule;
class PostController extends Controller
{
/**
* Sortable columns. Anything not in here is a 422, not a query.
*
* @var list<string>
*/
private const SORTABLE = ['created_at', 'title', 'view_count'];
public function index(Request $request): AnonymousResourceCollection
{
$filters = $request->validate([
'per_page' => ['integer', 'between:1,100'],
'sort' => ['string', Rule::in(self::SORTABLE)],
'direction' => ['string', Rule::in(['asc', 'desc'])],
'author' => ['integer', 'exists:users,id'],
'search' => ['string', 'max:100'],
]);
$posts = Post::query()
->with('author')
->when(
$filters['author'] ?? null,
fn ($query, int $authorId) => $query->where('user_id', $authorId),
)
->when(
$filters['search'] ?? null,
fn ($query, string $search) => $query->where('title', 'like', "%{$search}%"),
)
->orderBy(
$filters['sort'] ?? 'created_at',
$filters['direction'] ?? 'desc',
)
->paginate($filters['per_page'] ?? 15)
->withQueryString();
return PostResource::collection($posts);
}
}
When to use it
- Return 25 records per page from an index endpoint instead of the whole table.
- Back an infinite-scroll feed without showing a row twice when new rows arrive.
- Let clients sort by title or date, and reject any other column with a 422.
- Keep the active filters on the next-page link your client follows.
More examples
Cursor pagination for a feed, and the JSON it produces
Reach for cursorPaginate on an infinite scroll or an export: the client only ever needs the next page, and offset pagination would both slow down and duplicate rows as new posts arrive. Note that first and last are null, because a cursor paginator does not know where the end is.
<?php
// orderBy on a unique column is required: the cursor encodes the last row's
// values for those columns and the next query asks for rows beyond them.
$posts = Post::query()
->where('published_at', '<=', now())
->orderByDesc('id')
->cursorPaginate(20);
return PostResource::collection($posts);
// The response. There is no total and no page number to jump to:
//
// {
// "data": [ ... 20 posts ... ],
// "links": {
// "first": null,
// "last": null,
// "prev": null,
// "next": "https://example.test/api/posts?cursor=eyJpZCI6ODEsIl9..."
// },
// "meta": {
// "path": "https://example.test/api/posts",
// "per_page": 20,
// "next_cursor": "eyJpZCI6ODEsIl9...",
// "prev_cursor": null
// }
// }
The paging arithmetic, in plain Collection code
Clamping per_page matters as much as validating it: without a ceiling, one request for per_page=100000 is a full table scan serialised into JSON. This is also the arithmetic behind meta.last_page, which is why last_page is unavailable on simplePaginate, where total is never counted.
<?php
use Illuminate\Support\Number;
$ids = collect(range(1, 47));
// What the client asked for, and what we are willing to give.
$requestedPerPage = 500;
$perPage = Number::clamp($requestedPerPage, min: 1, max: 25);
$page = 2;
// forPage($page, $perPage) is slice((page - 1) * perPage, perPage):
// the same offset the SQL would use.
$items = $ids->forPage($page, $perPage);
$total = $ids->count();
echo "per_page: {$perPage}\n";
echo 'last_page: ' . (int) ceil($total / $perPage) . "\n";
echo "from: {$items->first()}, to: {$items->last()}\n";
// Output:
// per_page: 25
// last_page: 2
// from: 26, to: 47
Offset against cursor, in SQL
Useful when an index endpoint gets slow. The count query and the offset scan are the two costs, and they grow with the table and the page number respectively; the cursor query does not grow with either.
-- paginate(15) on page 3: a count, then a page the database
-- reaches by scanning and discarding 30 rows.
select count(*) as aggregate from "posts";
select * from "posts" order by "id" desc limit 15 offset 30;
-- simplePaginate(15) on page 3: no count. Asks for one extra row
-- to learn whether a next page exists.
select * from "posts" order by "id" desc limit 16 offset 30;
-- cursorPaginate(15) after id 81: no offset, no count. Index seek.
select * from "posts" where "id" < 81 order by "id" desc limit 16;
Discussion