Laravel API Pagination, Dynamic Filtering & Performance Sorting

When an API database grows to hundreds of thousands of records, returning entire datasets crashes client applications and exhausts server memory. Production REST APIs require Pagination (chunking results into manageable pages), Dynamic Filtering (querying by status, date, or author), and Multi-Column Sorting (ordering by price, date, or popularity).

1. Standard Offset Pagination vs Cursor Pagination

2. Implementing Clean Dynamic Query Filtering

Avoid messy if ($request->has(...)) chains in your controller by using Eloquent's when() helper:

public function index(Request $request)
{
    $articles = Article::query()
        // 1. Text Search Filter
        ->when($request->filled('search'), function ($query) use ($request) {
            $query->where(function ($q) use ($request) {
                $q->where('title', 'like', "%{$request->search}%")
                  ->orWhere('body', 'like', "%{$request->search}%");
            });
        })
        // 2. Status Filter
        ->when($request->filled('status'), fn ($q) => $q->where('status', $request->status))
        // 3. Category Filter
        ->when($request->filled('category_id'), fn ($q) => $q->where('category_id', $request->category_id))
        // 4. Dynamic Sorting with Whitelist
        ->when($request->filled('sort'), function ($query) use ($request) {
            $sortColumn = ltrim($request->sort, '-');
            $direction = str_starts_with($request->sort, '-') ? 'desc' : 'asc';
            
            // Security: Strictly whitelist allowed sort columns
            if (in_array($sortColumn, ['title', 'created_at', 'views'])) {
                $query->orderBy($sortColumn, $direction);
            }
        }, fn ($query) => $query->latest()) // Default sort
        ->cursorPaginate(20);

    return ArticleResource::collection($articles);
}

3. Enterprise Filtering with Spatie Query Builder

For enterprise APIs, the gold standard is spatie/laravel-query-builder. It supports standard JSON:API specifications (e.g. GET /api/v1/articles?filter[status]=published&sort=-created_at&include=author) in two lines of code:

use Spatie\QueryBuilder\QueryBuilder;
use Spatie\QueryBuilder\AllowedFilter;

$articles = QueryBuilder::for(Article::class)
    ->allowedFilters(['title', AllowedFilter::exact('status'), 'category_id'])
    ->allowedSorts(['title', 'created_at', 'price'])
    ->allowedIncludes(['author', 'comments'])
    ->paginate(15);