Laravel REST API: Advanced Filtering and Sorting with PostgreSQL 2026

Introduction: Why Laravel's Built-in Tools Are Not Enough

Laravel provides a convenient Eloquent ORM and built-in pagination, but as soon as clients start demanding filtering by ten fields, sorting by computed values, and cursor-based pagination for infinite scrolling — controllers turn into a mess of if ($request->has(...)). Teams without a systematic approach end up with: duplicated filtering logic across multiple controllers, SQL injections via dynamic sorting, N+1 queries, and a complete lack of documentation for the frontend team. In 2026, when a REST API serves a web client, a mobile app, and third-party integrators simultaneously, this is simply unacceptable.

In this article, we will build a fully featured filtering system in Laravel with PostgreSQL: from designing the API contract to query optimization and testing.

Designing the API Contract: Query Parameters and Conventions

Before writing any code, lock down the contract. Chaotic parameters like filterByStatus, status_filter, and status across different endpoints are the first sign of a missing system.

Recommended Conventions

Filters: filter[field]=value or filter[field][operator]=value
Sorting: sort=field for ASC, sort=-field for DESC (minus prefix as in JSON:API)
Pagination: page[size]=25&page[cursor]=eyJpZCI6MTAwfQ for cursor-based or page[number]=2&page[size]=25 for offset
Full-text search: search=query

Real-world URL examples:

GET /api/v1/products?filter[status]=active&filter[price][gte]=100&sort=-created_at&page[size]=20
GET /api/v1/products?search=wireless+headphones&filter[category_id]=5&sort=price
GET /api/v1/orders?filter[created_at][gte]=2026-01-01&filter[user_id]=42&sort=-total

This format is self-documenting, easy to parse on the client side, and scales without architectural changes.

The Filter/Scope Pattern in Laravel: Reusable Filter Classes

The core idea is to move filtering logic out of the controller into a dedicated class applied via an Eloquent local scope. The controller stays lean, and the logic can be tested in isolation.

Base Filter Class

<?php

namespace App\Http\Filters;

use Illuminate\Database\Eloquent\Builder;
use Illuminate\Http\Request;

abstract class AbstractFilter
{
    protected Request $request;
    protected Builder $builder;

    // Map: parameter name => handler method
    protected array $filters = [];

    public function __construct(Request $request)
    {
        $this->request = $request;
    }

    public function apply(Builder $builder): Builder
    {
        $this->builder = $builder;

        foreach ($this->filters as $param => $method) {
            $value = data_get($this->request->input('filter'), $param);
            if ($value !== null && $value !== '') {
                $this->$method($value);
            }
        }

        return $this->builder;
    }
}

Concrete Filter for the Product Model

<?php

namespace App\Http\Filters;

use Illuminate\Database\Eloquent\Builder;

class ProductFilter extends AbstractFilter
{
    protected array $filters = [
        'status'      => 'byStatus',
        'category_id' => 'byCategory',
        'price'       => 'byPrice',
        'created_at'  => 'byCreatedAt',
    ];

    protected function byStatus(string $value): void
    {
        $this->builder->where('status', $value);
    }

    protected function byCategory(int|string $value): void
    {
        $this->builder->where('category_id', (int) $value);
    }

    // Operator support: filter[price][gte]=100&filter[price][lte]=500
    protected function byPrice(array|string $value): void
    {
        if (is_array($value)) {
            $operators = ['gte' => '>=', 'lte' => '<=', 'gt' => '>', 'lt' => '<'];
            foreach ($operators as $key => $op) {
                if (isset($value[$key])) {
                    $this->builder->where('price', $op, (float) $value[$key]);
                }
            }
        } else {
            $this->builder->where('price', (float) $value);
        }
    }

    protected function byCreatedAt(array|string $value): void
    {
        if (is_array($value)) {
            if (isset($value['gte'])) {
                $this->builder->whereDate('created_at', '>=', $value['gte']);
            }
            if (isset($value['lte'])) {
                $this->builder->whereDate('created_at', '<=', $value['lte']);
            }
        }
    }
}

Trait for the Model and Scope

<?php

namespace App\Traits;

use App\Http\Filters\AbstractFilter;
use Illuminate\Database\Eloquent\Builder;

trait Filterable
{
    public function scopeFilter(Builder $query, AbstractFilter $filter): Builder
    {
        return $filter->apply($query);
    }
}

// In the Product model:
// use Filterable;

// In the controller:
// Product::filter($filter)->paginate();

The controller becomes concise:

<?php

public function index(Request $request, ProductFilter $filter)
{
    $products = Product::filter($filter)
        ->applySorting($request)
        ->cursorPaginate($request->input('page.size', 25));

    return ProductResource::collection($products);
}

Dynamic Sorting: Security First

Directly substituting $request->input('sort') into orderBy() is an SQL injection vulnerability. A whitelist approach is mandatory.

<?php

namespace App\Http\Sorts;

use Illuminate\Database\Eloquent\Builder;
use Illuminate\Http\Request;

class ProductSorter
{
    // Allowed fields and their database aliases
    protected array $allowedSorts = [
        'price'      => 'price',
        'created_at' => 'created_at',
        'name'       => 'name',
        'rating'     => 'average_rating', // alias for a computed field
    ];

    public function apply(Builder $query, Request $request): Builder
    {
        $sortParam = $request->input('sort', '-created_at');
        $direction = str_starts_with($sortParam, '-') ? 'desc' : 'asc';
        $field = ltrim($sortParam, '-');

        if (!array_key_exists($field, $this->allowedSorts)) {
            // Ignore invalid sort, apply default
            return $query->orderBy('created_at', 'desc');
        }

        return $query->orderBy($this->allowedSorts[$field], $direction)
                     ->orderBy('id', $direction); // tiebreaker for stable sorting
    }
}

The id tiebreaker is critically important for correct cursor-based pagination — without it, the order of records sharing the same sort field value is unpredictable.

Cursor Pagination vs Offset Pagination

Offset pagination (LIMIT 25 OFFSET 500) is simple, but with large datasets PostgreSQL is forced to count and discard the first 500 rows. With millions of records, this degrades into a full scan.

Cursor pagination uses a WHERE condition based on the last record's value: WHERE (created_at, id) < ('2026-01-15', 1000). PostgreSQL uses the index directly — O(log n) instead of O(n).

Laravel 8+ has a built-in cursorPaginate(), but it only supports sorting by a single field. For composite sorting, implement it manually:

<?php

namespace App\Services;

use Illuminate\Database\Eloquent\Builder;
use Illuminate\Support\Str;

class CursorPaginator
{
    public function paginate(Builder $query, int $perPage, ?string $cursor): array
    {
        if ($cursor) {
            $decoded = json_decode(base64_decode($cursor), true);
            // Composite condition: (sort_field, id) < (value, id)
            $query->where(function ($q) use ($decoded) {
                $q->where('created_at', '<', $decoded['created_at'])
                  ->orWhere(function ($q2) use ($decoded) {
                      $q2->where('created_at', $decoded['created_at'])
                         ->where('id', '<', $decoded['id']);
                  });
            });
        }

        $items = $query->limit($perPage + 1)->get();
        $hasMore = $items->count() > $perPage;
        $items = $items->take($perPage);

        $nextCursor = null;
        if ($hasMore && $last = $items->last()) {
            $nextCursor = base64_encode(json_encode([
                'created_at' => $last->created_at->toISOString(),
                'id'         => $last->id,
            ]));
        }

        return ['data' => $items, 'next_cursor' => $nextCursor, 'has_more' => $hasMore];
    }
}

When to use offset: admin panels where jumping to a specific page is needed. Cursor pagination is best for infinite scroll and high-traffic public APIs.

Leveraging PostgreSQL Features

Full-Text Search with tsvector

Instead of LIKE '%query%' (sequential scan), use PostgreSQL's built-in full-text search:

-- Migration: add column and index
ALTER TABLE products ADD COLUMN search_vector tsvector
    GENERATED ALWAYS AS (
        to_tsvector('english', coalesce(name, '') || ' ' || coalesce(description, ''))
    ) STORED;

CREATE INDEX products_search_vector_idx ON products USING GIN(search_vector);

<?php

// In the filter or scope:
protected function search(string $query): void
{
    $this->builder->whereRaw(
        "search_vector @@ plainto_tsquery('english', ?)",
        [$query]
    )->orderByRaw(
        "ts_rank(search_vector, plainto_tsquery('english', ?)) DESC",
        [$query]
    );
}

Filtering by JSONB Fields

<?php

// Filter by nested JSON: filter[attributes][color]=red
protected function byAttributes(array $value): void
{
    foreach ($value as $key => $val) {
        $this->builder->whereRaw(
            "attributes->>? = ?",
            [$key, $val]
        );
    }
}

// Index for JSONB:
// CREATE INDEX products_attributes_gin ON products USING GIN(attributes);

Query Optimization: EXPLAIN ANALYZE and Indexes

Every frequently used filter should be covered by an index. Analyze query plans:

-- Run in psql or via Laravel DB::select()
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM products
WHERE status = 'active'
  AND category_id = 5
  AND price BETWEEN 100 AND 500
ORDER BY created_at DESC, id DESC
LIMIT 25;

A composite index for a typical filter combination:

-- Column order: equality filters first, then range, then sort
CREATE INDEX products_listing_idx
    ON products (status, category_id, price, created_at DESC, id DESC)
    WHERE status = 'active'; -- partial index saves space

In Laravel, select only the fields you need:

<?php

Product::filter($filter)
    ->select(['id', 'name', 'price', 'status', 'created_at', 'thumbnail_url'])
    ->with(['category:id,name']) // eager loading only required fields
    ->cursorPaginate(25);

Caching Filter Results

Caching an API with variable parameters is a non-trivial task. Don't cache everything blindly: for unique filter combinations, a cache is useless and will pollute Redis.

Strategy: Cache Only "Hot" Requests

<?php

namespace App\Services;

use Illuminate\Support\Facades\Cache;
use Illuminate\Http\Request;

class FilterCacheService
{
    // Cache only if the request has no user-specific filters
    // (catalog pages, homepage) — TTL 5 minutes
    public function remember(Request $request, callable $callback): mixed
    {
        $filterParams = $request->input('filter', []);
        $hasUserSpecificFilter = isset($filterParams['user_id']);

        if ($hasUserSpecificFilter || count($filterParams) > 3) {
            return $callback(); // no cache
        }

        $cacheKey = 'products:' . md5($request->getQueryString());

        return Cache::tags(['products'])->remember($cacheKey, 300, $callback);
    }

    // Invalidate when data changes:
    public static function flush(): void
    {
        Cache::tags(['products'])->flush();
    }
}

For Redis, use tagged cache (Cache::tags()) — this allows you to flush all entries with the products tag whenever the catalog changes via an Observer.

Documenting the Filtering API: OpenAPI/Swagger

Without documentation, the frontend team will be guessing at parameters. Use the darkaonline/l5-swagger package with PHP annotations:

<?php

/**
 * @OA\Get(
 *     path="/api/v1/products",
 *     summary="List products with filtering",
 *     tags={"Products"},
 *     @OA\Parameter(
 *         name="filter[status]",
 *         in="query",
 *         description="Product status: active, inactive, draft",
 *         @OA\Schema(type="string", enum={"active", "inactive", "draft"})
 *     ),
 *     @OA\Parameter(
 *         name="filter[price][gte]",
 *         in="query",
 *         description="Minimum price",
 *         @OA\Schema(type="number")
 *     ),
 *     @OA\Parameter(
 *         name="sort",
 *         in="query",
 *         description="Sort field. Use '-' prefix for DESC. Available: price, created_at, name, rating",
 *         @OA\Schema(type="string", example="-created_at")
 *     ),
 *     @OA\Parameter(
 *         name="page[cursor]",
 *         in="query",
 *         description="Cursor for the next page (from the next_cursor field of the previous response)",
 *         @OA\Schema(type="string")
 *     ),
 *     @OA\Response(response=200, description="OK")
 * )
 */
public function index(Request $request, ProductFilter $filter): JsonResponse
{
    // ...
}

Testing: Unit and Feature Tests

Test each filter in isolation (unit) and the endpoint behavior as a whole (feature).

<?php

namespace Tests\Unit\Filters;

use App\Http\Filters\ProductFilter;
use App\Models\Product;
use Illuminate\Foundation\Testing\RefreshDatabase;
use Illuminate\Http\Request;
use Tests\TestCase;

class ProductFilterTest extends TestCase
{
    use RefreshDatabase;

    public function test_filters_by_status(): void
    {
        Product::factory()->count(3)->create(['status' => 'active']);
        Product::factory()->count(2)->create(['status' => 'inactive']);

        $request = Request::create('/', 'GET', ['filter' => ['status' => 'active']]);
        $filter = new ProductFilter($request);

        $result = Product::filter($filter)->get();

        $this->assertCount(3, $result);
        $this->assertTrue($result->every(fn($p) => $p->status === 'active'));
    }

    public function test_filters_by_price_range(): void
    {
        Product::factory()->create(['price' => 50]);
        Product::factory()->create(['price' => 200]);
        Product::factory()->create(['price' => 600]);

        $request = Request::create('/', 'GET', [
            'filter' => ['price' => ['gte' => '100', 'lte' => '500']]
        ]);
        $filter = new ProductFilter($request);

        $result = Product::filter($filter)->get();

        $this->assertCount(1, $result);
        $this->assertEquals(200, $result->first()->price);
    }

    public function test_invalid_sort_field_falls_back_to_default(): void
    {
        $request = Request::create('/', 'GET', ['sort' => 'malicious_field; DROP TABLE products;--']);
        // Ensure the request executes without an exception
        $response = $this->getJson('/api/v1/products?sort=malicious_field');
        $response->assertOk();
    }
}

Conclusion: Quality API Filtering Checklist

✅ A unified API contract is established and documented in OpenAPI
✅ Filtering logic is moved to dedicated Filter classes, not the controller
✅ Whitelist of allowed sort fields — protection against SQL injection
✅ Cursor-based pagination for public endpoints with large data volumes
✅ Full-text search via tsvector instead of LIKE
✅ PostgreSQL composite indexes tailored to real filter combinations
✅ EXPLAIN ANALYZE verified for the top 5 queries
✅ SELECT only required fields, eager loading without N+1
✅ Tagged cache with invalidation on model events
✅ Unit tests for every filter, feature tests for endpoints

A systematic approach to filtering is not premature optimization. It is an architectural decision that determines whether your Laravel REST API can handle growing traffic without a full refactor six months down the road. PostgreSQL provides powerful tools: GIN indexes, tsvector, JSONB — use them deliberately, guided by real query plans rather than intuition.

Laravel and REST API: Advanced Filtering and Sorting System with PostgreSQL