Filtering, Search, Sorting and Pagination in Laravel 13

Lesson 13 — Filtering, Search, Sorting and Pagination: build a task-list query with an explicit contract, continuing from lesson 12. This query runs in tests; no public data route is exposed before authentication and policies exist.

1. Define the list contract

A server-selected Project always scopes the list. Status accepts todo, doing or done; q searches titles with at most 100 characters; sort accepts newest, oldest or title. Page size ranges from 1 to 50 with a default of 20, and page ranges from 1 to 10,000. These are TaskFlow example choices, not Laravel's hard limits.

Never pass arbitrary client sorting expressions into orderByRaw(). Value binding does not make arbitrary column names or SQL expressions safe. A fixed sort vocabulary also makes URLs predictable.

2. Implement the query

Create app/Queries/TaskListQuery.php:

<?php

namespace App\Queries;

use App\Models\Project;
use Illuminate\Contracts\Pagination\LengthAwarePaginator;
use Illuminate\Support\Facades\Validator;
use Illuminate\Validation\Rule;

class TaskListQuery
{
    public function paginate(Project $project, array $input): LengthAwarePaginator
    {
        $filters = Validator::make($input, [
            'status' => ['nullable', Rule::in(['todo', 'doing', 'done'])],
            'q' => ['nullable', 'string', 'max:100'],
            'sort' => ['sometimes', Rule::in(['newest', 'oldest', 'title'])],
            'per_page' => ['sometimes', 'integer', 'min:1', 'max:50'],
            'page' => ['sometimes', 'integer', 'min:1', 'max:10000'],
        ])->validate();

        $query = $project->tasks()->with('assignee:id,name');
        if (! empty($filters['status'])) {
            $query->where('status', $filters['status']);
        }
        if (isset($filters['q']) && trim($filters['q']) !== '') {
            // LIKE wildcards are intentionally supported in this course example.
            $query->where('title', 'like', '%'.trim($filters['q']).'%');
        }
        match ($filters['sort'] ?? 'newest') {
            'oldest' => $query->orderBy('id'),
            'title' => $query->orderBy('title')->orderBy('id'),
            default => $query->orderByDesc('id'),
        };

        return $query->paginate((int) ($filters['per_page'] ?? 20), ['*'], 'page', (int) ($filters['page'] ?? 1))
            ->appends(array_diff_key($filters, ['page' => true]));
    }
}

Validation sits at the array-input boundary so tests and other callers share the same contract. No controller uses it yet; tests examine ValidationException directly. HTTP integration must define form versus JSON error behavior as discussed in lesson 7.

Starting from $project->tasks() preserves project scope rather than relying on each caller to remember a project_id condition. The Project itself must still be selected from an authorized scope. Data scoping does not replace permission checks.

3. LIKE search is not full-text search

The search pattern is passed as a bound value, not concatenated into raw SQL. However, % and _ remain LIKE wildcards. This contract deliberately allows q=% to match all titles, and the test documents that. Literal wildcard search requires engine-appropriate escaping and tests, not merely a different UI label.

A leading wildcard often prevents ordinary B-tree prefix lookup. Case, Vietnamese accents and collations may behave differently across SQLite, MySQL and PostgreSQL. This example promises neither natural-language search nor identical results on every engine.

4. Stable order does not freeze the dataset

Title ordering adds ID as a tie-breaker; newest/oldest already order by unique IDs. This resolves equal sort values but does not create a snapshot between page requests. Concurrent inserts or deletes can still cause offset pagination to repeat or skip records.

paginate provides totals, and counting has a cost. Consider simplePaginate when only previous/next navigation is needed, or cursor pagination for suitable large ordered datasets. Switching strategies changes the URL and interaction contract, not just a method name.

5. Preserve validated filters in links

appends receives only validated filters, excluding the previous page parameter. Do not copy arbitrary query strings containing unrelated inputs or secrets. After authorization is integrated, a view can render $tasks->links(); GET filter forms produce shareable URLs and should reset to page 1 when filters change.

Assignees are eager loaded with id/name to avoid a row-by-row lookup. Tasks retain assignee_id. Unassigned tasks have a null relationship, so future UI must handle that state rather than unconditionally accessing name.

6. Test isolation, order and invalid input

Create tests/Feature/TaskListQueryTest.php:

<?php

namespace Tests\Feature;

use App\Models\Project;
use App\Models\Task;
use App\Queries\TaskListQuery;
use Illuminate\Support\Facades\DB;
use Illuminate\Validation\ValidationException;
use Tests\TestCase;

class TaskListQueryTest extends TestCase
{
    protected function setUp(): void
    {
        parent::setUp();
        config(['database.default' => 'sqlite', 'database.connections.sqlite.database' => ':memory:',
            'database.connections.sqlite.url' => null]);
        DB::purge('sqlite');
        $this->artisan('migrate', ['--force' => true])->assertSuccessful();
    }

    public function test_filters_remain_project_scoped_and_order_is_stable(): void
    {
        $project = Project::factory()->create();
        $first = Task::factory()->for($project)->create(['title' => 'Review code']);
        $second = Task::factory()->for($project)->create(['title' => 'Review code']);
        Task::factory()->for($project)->done()->create(['title' => 'Review done']);
        Task::factory()->create(['title' => 'Review outsider']);

        $page = app(TaskListQuery::class)->paginate($project, [
            'status' => 'todo', 'q' => 'Review', 'sort' => 'title', 'per_page' => 1,
        ]);
        $this->assertSame(2, $page->total());
        $this->assertSame($first->id, $page->items()[0]->id);
        $this->assertTrue($page->items()[0]->relationLoaded('assignee'));
        $next = app(TaskListQuery::class)->paginate($project, [
            'status' => 'todo', 'q' => 'Review', 'sort' => 'title', 'per_page' => 1, 'page' => 2,
        ]);
        $this->assertSame($second->id, $next->items()[0]->id);
        $this->assertStringContainsString('status=todo', $page->nextPageUrl());
    }

    public function test_untrusted_sort_and_excessive_page_size_are_rejected(): void
    {
        $project = Project::factory()->create();
        try {
            app(TaskListQuery::class)->paginate($project, ['sort' => 'title desc; drop table tasks', 'per_page' => 1000]);
            $this->fail('Expected validation errors');
        } catch (ValidationException $exception) {
            $this->assertArrayHasKey('sort', $exception->errors());
            $this->assertArrayHasKey('per_page', $exception->errors());
        }
    }

    public function test_empty_results_and_wildcard_contract(): void
    {
        $project = Project::factory()->create();
        Task::factory()->for($project)->create(['title' => 'Write test']);
        $query = app(TaskListQuery::class);
        $this->assertSame(0, $query->paginate($project, ['q' => 'absent'])->total());
        $this->assertSame(1, $query->paginate($project, ['q' => '%'])->total());
    }
}

php artisan test --filter=TaskListQueryTest
php artisan test

The full suite passed 33 tests with 112 assertions. Fixtures include an outsider task, a done task excluded by the filter and duplicate titles. Assertions verify scope and tie-breaking, not merely non-empty results. Other checks reject unknown sorting and excessive page sizes and document empty and wildcard searches.

7. Exercise before UI integration

Test a page beyond the last available page and q="0" to detect truthiness mistakes. Add an unassigned task and verify its relationship is loaded. Measure queries on the actual fixture: count, page reads and eager loading mean paginate does not universally execute just two queries.

Before real HTTP exposure, add authentication, policies, useful validation feedback and appropriate rate limits. A page-number cap does not prevent every expensive query. Refer to Laravel pagination when choosing the product's navigation model.

Navigation: Lesson 12 · Roadmap. Next comes transactions and concurrent updates.