Laravel API Pagination, Filtering and Sorting Done Right

A list endpoint looks trivial until it meets real traffic: someone requests per_page=1000000, a client pages through a table that is being written to and sees the same row twice, or ?sort= turns into a way to order by a column you never meant to expose. Doing it properly comes down to four decisions: a stable URL contract, strict validation with allowlists, a deterministic sort order, and the right paginator for the table size. I tested each of those against a 1,000,000-row MariaDB table using the real Laravel 13.35.0 framework code, and the numbers below come from that run.

Decide the URL contract first

Pick one convention and keep it identical across every list endpoint. This is the one used in the rest of the article:

GET /api/orders?filter[status]=paid&filter[created_from]=2024-03-01&sort=-created_at,total&per_page=25&page=2
GET /api/orders?sort=-created_at&per_page=25&cursor=eyJ...

• filter[field]=value for filters. Grouping them under one key keeps them from colliding with sort, page and per_page.
• sort=field for ascending, sort=-field for descending, comma separated for multiple keys.
• per_page with a hard maximum, and either page or cursor depending on the paginator.

The same shape is used by the Spatie laravel-query-builder package (/users?filter[name]=John with allowedFilters('name'), and /users?sort=id with allowedSorts('id')), so if you prefer a package over hand-written code you can adopt it later without changing your public API. The hand-written version below is short enough that you may not need it, and it makes the safety rules visible.

Validate everything, allow only what you list

Every query-string value is untrusted input. Put the rules in a form request so a bad value returns a 422 with a clear message, not a 500 from the database. This class is in app/Http/Requests/ListOrdersRequest.php:

<?php

namespace App\Http\Requests;

use Illuminate\Foundation\Http\FormRequest;
use Illuminate\Validation\Rule;

class ListOrdersRequest extends FormRequest
{
    public function authorize(): bool
    {
        return true; // authorize via policy/middleware as usual
    }

    public function rules(): array
    {
        return [
            'filter'              => ['sometimes', 'array'],
            'filter.status'       => ['sometimes', Rule::in(['pending', 'paid', 'shipped', 'cancelled'])],
            'filter.user_id'      => ['sometimes', 'integer', 'min:1'],
            'filter.created_from' => ['sometimes', 'date_format:Y-m-d'],
            'filter.created_to'   => ['sometimes', 'date_format:Y-m-d', 'after_or_equal:filter.created_from'],
            'filter.q'            => ['sometimes', 'string', 'max:50'],

            'sort'                => ['sometimes', 'string', 'max:100'],

            'per_page'            => ['sometimes', 'integer', 'between:1,100'],
            'page'                => ['sometimes', 'integer', 'min:1'],
            'cursor'              => ['sometimes', 'string'],
        ];
    }
}

Two things are worth noting. The per_page ceiling is not optional: Laravel does not clamp it for you. In Builder::paginate() the value you pass is used as-is (it only falls back to the model default when it is falsy), so an unclamped per_page goes straight into LIMIT. In my test, simplePaginate(200000) returned 200,000 rows in about 4.2 seconds and peaked at 318 MB of PHP memory, from a single request. Second, the page value is already forgiving on its own: the framework's page resolver returns 1 for anything that is not an integer of at least 1 (abc, 0, -3). The rule above still gives API clients an honest error instead of silently serving page 1.

Filtering with when()

Filters are plain, bound where clauses added only when the client sent the value. when() keeps that readable:

$filters = $request->validated('filter', []);

$query = Order::query()
    ->when($filters['status'] ?? null, fn ($q, $v) => $q->where('status', $v))
    ->when($filters['user_id'] ?? null, fn ($q, $v) => $q->where('user_id', $v))
    ->when($filters['created_from'] ?? null, fn ($q, $v) => $q->where('created_at', '>=', $v.' 00:00:00'))
    ->when($filters['created_to'] ?? null, fn ($q, $v) =>
        $q->where('created_at', '<', Carbon::parse($v)->addDay()->toDateString().' 00:00:00'))
    ->when($filters['q'] ?? null, fn ($q, $v) =>
        $q->where('reference', 'like', addcslashes($v, '%_\\').'%'));

With every filter present, that produced this SQL and bindings (checked against the real query builder):

select * from `orders` where `status` = ? and `user_id` = ? and `created_at` >= ? and `created_at` < ? and `reference` like ?
-- bindings: paid, 42, 2024-03-01 00:00:00, 2024-04-01 00:00:00, ORD-12%

A few choices in there are deliberate:

• The end date is exclusive of the next day, not <= '2024-03-31'. A date-only comparison against a datetime column silently drops everything after midnight on the last day.
• The search is a prefix match (term%), and %, _ and \ in the user's text are escaped so they cannot act as wildcards. A leading wildcard (%term%) cannot use a normal B-tree index. On the test table, reference like '%1234%' was a full scan (type: ALL, about 996,000 rows examined), while like 'ORD-1%' used an index on reference once one existed. If you need real substring search, use a FULLTEXT index or a search engine, not LIKE.
• Filter values are always bindings. User input never becomes part of the SQL string here.

Sorting: allowlist the fields and always add a tie-breaker

Do not pass $request->sort to orderBy(). Bindings cannot be used for column names, so the framework wraps whatever you pass as an identifier. I tried hostile input against orderBy() and the builder produced these (no injection, but see the problems):

orderBy('user_id; drop table orders')
  => order by `user_id; drop table orders` asc
orderBy('created_at desc, (select sleep(5))')
  => order by `created_at desc, (select sleep(5))` asc

The identifier quoting does its job, so this is not an injection hole. The problem is that the client still controls which column is used: it can order by any column in the table, including ones you never return, which leaks information about their values through the ordering, and any unknown name becomes a database error and a 500. The direction argument is validated by the framework (an invalid one throws InvalidArgumentException), but the column is not. So map public names to real columns yourself:

<?php

namespace App\Support;

use Illuminate\Database\Eloquent\Builder;
use Illuminate\Validation\ValidationException;

final class ApplySort
{
    /**
     * @param  array<string, string>  $allowed  public name => real column
     */
    public static function to(Builder $query, ?string $sort, array $allowed, string $default = '-created_at'): Builder
    {
        $used = [];
        $direction = 'asc';

        foreach (array_filter(explode(',', $sort ?: $default)) as $field) {
            $direction = str_starts_with($field, '-') ? 'desc' : 'asc';
            $name = ltrim($field, '-');

            if (! isset($allowed[$name])) {
                throw ValidationException::withMessages([
                    'sort' => "Unsupported sort field: {$name}. Allowed: ".implode(', ', array_keys($allowed)),
                ]);
            }

            if (isset($used[$name])) {
                continue; // ignore duplicates such as sort=total,-total
            }

            $used[$name] = true;
            $query->orderBy($allowed[$name], $direction);
        }

        // Unique tie-breaker so pages never overlap or skip rows.
        if (! isset($used['id'])) {
            $query->orderBy($query->getModel()->getQualifiedKeyName(), $direction);
        }

        return $query;
    }
}

Used with a map of allowed fields, the results were (I ran the same logic with a plain InvalidArgumentException in place of ValidationException, which is the only difference):

sort value	Resulting ORDER BY
(none, default -created_at)	created_at desc, orders.id desc
-total	total_cents desc, orders.id desc
total,-created_at	total_cents asc, created_at desc, orders.id desc
reference (not allowed)	rejected

Why the tie-breaker matters

If you sort by a column that is not unique, such as created_at with several rows in the same second, the database is free to return tied rows in any order, and it may choose differently for each page's query. I reproduced it with nine rows sharing one timestamp, paged three at a time with orderBy('created_at') only:

page 1: t9, t2, t3
page 2: t4, t5, t6
page 3: t7, t8, t9

Row t1 never appeared and t9 appeared twice. Adding orderBy('id') as the last key gave t1,t2,t3 | t4,t5,t6 | t7,t8,t9. That specific misordering is how MariaDB behaved in my run; the SQL standard simply makes no promise about tie order, which is the real reason to add the tie-breaker. Cursor pagination also requires it, as covered next.

Choosing the paginator

Laravel offers three, and they differ in what they cost and what they promise. These are the queries and response keys I captured from the real framework with orderBy('id') and 25 rows per page:

Method	Queries	Response contains	Page numbers
paginate()	select count(*) plus limit 25 offset N	total, last_page, from, to, page links	Yes
simplePaginate()	One: limit 26 offset N	next_page_url, prev_page_url, no totals	Next/previous only
cursorPaginate()	One: where (id > ?) order by id limit 26	next_cursor, prev_cursor, URLs	No

Timings on 1,000,000 rows (best of three, local MariaDB 10.11, 2 vCPU container, so read them as relative, not absolute):

Request	paginate	simplePaginate	cursorPaginate
Page 1	163 ms	1.1 ms	-
Page 1,000	157 ms	7.1 ms	-
Page 20,000	263 ms	115 ms	-
Page 39,000 (the last)	430 ms	223 ms	2.0 ms (same position, via cursor)

Two effects show up. First, paginate() pays for count(*) on every request, roughly 150 to 190 ms here, and with status = 'paid' the count alone took about 190 ms. That cost is paid on page 1, which is the page users hit most. Second, offset pagination gets slower the deeper you go, because the database still walks and discards every skipped row. Laravel's documentation says the same: offset pagination scans previously matched rows, and cursor pagination is the most efficient of the three for large datasets.

Offset pagination can skip or repeat rows

A cursor says "continue after this row", an offset says "skip this many rows", and those differ when data changes between requests. I loaded ten rows, took page 1 (4 rows, newest first), inserted one newer row, then asked for page 2:

offset page 1:                r10, r9, r8, r7
offset page 2 after insert:   r7, r6, r5, r4     (r7 is repeated)
cursor page 2 after insert:   r6, r5, r4, r3     (continues correctly)

The framework docs describe this limitation too: offset pagination may skip or duplicate records when rows are written frequently.

What cursor pagination requires

From the Laravel docs, the cursor query must be ordered by columns from the table being paginated, at least one of the ordering columns must be unique (or the combination must be), null values are not supported in the ordering columns, and there are no page numbers or totals. Because you can sort by several columns, the unique id from ApplySort satisfies the rule. With sort=-created_at and a status filter, the second-page query was:

select * from `orders`
where `status` = ?
  and (`created_at` < ? or (`created_at` = ? and (`orders`.`id` < ?)))
order by `created_at` desc, `orders`.`id` desc
limit 4

The cursor string itself is only base64url-encoded JSON. Decoded, it looked like {"created_at":"2024-10-27 08:57:54","orders.id":999989,"_pointsToNextItems":true}. It is not signed or encrypted, so a client can read your column names and craft values. That is fine for filtering by position, but never treat a cursor as a secret or as authorization.

A practical rule

• Admin screens and small tables: paginate() if users need page numbers and a total, but accept the count cost.
• Public or mobile APIs, feeds, anything that grows: cursorPaginate(). It is both the fastest and the only one that stays consistent while rows are inserted.
• You need "load more" and an offset, but not totals: simplePaginate() skips the count query.

Indexes decide whether any of this is fast

Sorting is only cheap when an index matches the order. On the test table, this query with no suitable index was a full scan plus a filesort and took about 324 ms:

select * from orders where status = 'paid' order by created_at desc, id desc limit 25;
-- EXPLAIN without a useful index: type ALL, rows ~996,000, Extra: Using where; Using filesort

After adding index(created_at) or the composite index(status, created_at, id), the same query ran in about 0.6 ms. With only created_at indexed, MariaDB read the index in order and stopped after 25 rows (rows: 25). With the composite index, it used a ref lookup on status. In InnoDB every secondary index implicitly includes the primary key, which is why a single-column created_at index is enough for created_at desc, id desc.

Schema::table('orders', function (Blueprint $table) {
    $table->index(['status', 'created_at', 'id']);
});

The general rule: put equality filters first, then the sort columns, in the same order as your ORDER BY. Add indexes for the filter and sort combinations your clients actually use, check them with EXPLAIN, and do not index every allowed field "just in case". Each index costs write time and disk space.

Putting it together

The controller stays thin. cursorPaginate() is used here; swap in paginate() if you need totals:

<?php

namespace App\Http\Controllers\Api;

use App\Http\Controllers\Controller;
use App\Http\Requests\ListOrdersRequest;
use App\Http\Resources\OrderResource;
use App\Models\Order;
use App\Support\ApplySort;
use Illuminate\Support\Carbon;

class OrderController extends Controller
{
    private const SORTS = [
        'created_at' => 'created_at',
        'total'      => 'total_cents',
        'id'         => 'id',
    ];

    public function index(ListOrdersRequest $request)
    {
        $filters = $request->validated('filter', []);

        $query = Order::query()
            ->when($filters['status'] ?? null, fn ($q, $v) => $q->where('status', $v))
            ->when($filters['user_id'] ?? null, fn ($q, $v) => $q->where('user_id', $v))
            ->when($filters['created_from'] ?? null, fn ($q, $v) => $q->where('created_at', '>=', $v.' 00:00:00'))
            ->when($filters['created_to'] ?? null, fn ($q, $v) =>
                $q->where('created_at', '<', Carbon::parse($v)->addDay()->toDateString().' 00:00:00'))
            ->when($filters['q'] ?? null, fn ($q, $v) =>
                $q->where('reference', 'like', addcslashes($v, '%_\\').'%'));

        ApplySort::to($query, $request->validated('sort'), self::SORTS);

        $orders = $query
            ->cursorPaginate($request->validated('per_page', 25))
            ->withQueryString();

        return OrderResource::collection($orders);
    }
}

withQueryString() matters: without it, the next and previous links drop your filters and sort, and the client lands on an unfiltered list. It is documented as the way to append the current query string to the pagination links. For a paginated resource collection, Laravel's documentation states that the response always contains data, links and meta. For offset pagination the documented shape is:

{
  "data": [ { "id": 1 }, { "id": 2 } ],
  "links": { "first": "http://example.com/users?page=1", "last": "http://example.com/users?page=1", "prev": null, "next": null },
  "meta": { "current_page": 1, "from": 1, "last_page": 1, "path": "http://example.com/users", "per_page": 15, "to": 10, "total": 10 }
}

If you want to change those keys, define a paginationInformation($request, $paginated, $default) method on the resource. I did not run the resource layer end to end in my test setup (see the last section), so confirm the exact cursor meta keys in your own app with one request before you document the API for clients.

Common problems

Symptom	Cause	Fix
Next link loses filters	Query string not appended	->withQueryString() on the paginator
Same row on two pages, or a row never seen	Non-unique sort, or offset paging on changing data	Unique tie-breaker; switch to cursor pagination
Page 1 is slow on a big table	count(*) on every request	simplePaginate() or cursorPaginate()
Deep pages get slower and slower	Offset scans skipped rows	Cursor pagination, or limit the maximum page
500 error on ?sort=foo	Unvalidated column name reaches the database	Allowlist and return 422
One request uses hundreds of MB	No per_page ceiling	between:1,100 in the form request
Cursor pagination throws or misbehaves	No unique column, null values, or ordering by a joined column	Add id as the last key; avoid nullable sort columns

Frequently asked questions

Should I use paginate() or cursorPaginate() for an API?

Use cursorPaginate() unless a client really needs page numbers or a total. It avoids the count query, does not slow down with depth, and stays correct when rows are inserted between requests.

What happens when page is beyond the last page?

The offset query returns no rows, so you get an empty data array rather than a 404. If your clients rely on a 404 or 422 for that, you need to check currentPage() > lastPage() yourself.

Is a package like Spatie's laravel-query-builder better than writing this by hand?

It is a reasonable choice if you have many list endpoints and want the same filter[...] and sort= contract everywhere, since it handles allowlists for you. The hand-written approach here has no dependency and is easier to bend to unusual rules. The key point is the same either way: allowlist fields and keep a deterministic order.

Can I sort by a relationship's column?

Not directly with cursor pagination, which needs ordering columns from the paginated table. Use a join or a subquery and expose the value as a selected, aliased column, or accept offset pagination for that endpoint.

References

• Laravel 13.x: Pagination
• Laravel 13.x: Eloquent API Resources (Pagination)
• Spatie laravel-query-builder: Introduction

---

Originally published on DEV Talk.