Laravel Query Builder

Laravel Query Builder

When to Apply

Activate this skill when:

• Building API endpoints that accept filter, sort, include, or fields query parameters
• Configuring allowed filters, sorts, includes, or field selections
• Creating custom filter, sort, or include classes
• Troubleshooting query builder exceptions or unexpected query results

Basic Usage

use Spatie\QueryBuilder\QueryBuilder;
use Spatie\QueryBuilder\AllowedFilter;
use Spatie\QueryBuilder\AllowedInclude;
use Spatie\QueryBuilder\AllowedSort;

// GET /users?filter[name]=John&sort=-created_at&include=posts
$users = QueryBuilder::for(User::class)
    ->allowedFilters('name', 'email')
    ->allowedSorts('name', 'created_at')
    ->allowedIncludes('posts', 'permissions')
    ->allowedFields('id', 'name', 'email')
    ->get();

Filtering

Filter Types

use Spatie\QueryBuilder\AllowedFilter;

QueryBuilder::for(User::class)
    ->allowedFilters(
        AllowedFilter::partial('name'),           // WHERE name LIKE '%value%' (default)
        AllowedFilter::exact('email'),             // WHERE email = 'value'
        AllowedFilter::beginsWith('name'),         // WHERE name LIKE 'value%'
        AllowedFilter::endsWith('name'),           // WHERE name LIKE '%value'
        AllowedFilter::scope('active'),            // Calls scopeActive()
        AllowedFilter::callback('search', fn ($query, $value) => ...),
        AllowedFilter::exact('role')->default('user'),  // Default filter value
        AllowedFilter::exact('status')->nullable(),     // Allows null values
        AllowedFilter::exact('role')->ignore('admin'),  // Ignores specific values
        AllowedFilter::belongsTo('author'),             // Filter by BelongsTo relationship
        AllowedFilter::trashed(),                       // Filter soft deletes (with, only, without)
    );

Operator Filters

use Spatie\QueryBuilder\AllowedFilter;
use Spatie\QueryBuilder\Enums\FilterOperator;

QueryBuilder::for(User::class)
    ->allowedFilters(
        AllowedFilter::operator('salary', FilterOperator::GreaterThan),
        AllowedFilter::operator('age', FilterOperator::LessThanOrEqual),
        AllowedFilter::operator('salary', FilterOperator::Dynamic), // Operator from request
    );

// GET /users?filter[salary]=gt:50000 (with Dynamic operator)

Relation Filters

Filter by related model properties using dot notation:

// GET /users?filter[posts.title]=Laravel
QueryBuilder::for(User::class)
    ->allowedFilters(AllowedFilter::partial('posts.title'));

Custom Column Names

// GET /users?filter[email]=john
// Queries the 'user_email' column
AllowedFilter::exact('email', 'user_email');

Custom Filters

Implement Spatie\QueryBuilder\Filters\Filter:

use Spatie\QueryBuilder\Filters\Filter;
use Illuminate\Database\Eloquent\Builder;

class FiltersUserPermission implements Filter
{
    public function __invoke(Builder $query, mixed $value, string $property): void
    {
        $query->whereHas('permissions', fn ($q) => $q->where('name', $value));
    }
}

AllowedFilter::custom('permission', new FiltersUserPermission());

Sorting

use Spatie\QueryBuilder\AllowedSort;
use Spatie\QueryBuilder\Enums\SortDirection;

QueryBuilder::for(User::class)
    ->allowedSorts(
        'name',
        'created_at',
        AllowedSort::field('order', 'sort_order'),   // Alias: ?sort=order queries sort_order
        AllowedSort::custom('popular', new SortMostPopular()),
        AllowedSort::callback('random', fn ($query, $descending) => $query->inRandomOrder()),
    )
    ->defaultSort('name')
    ->defaultSorts('name', AllowedSort::field('date', 'created_at')->defaultDirection(SortDirection::Descending));

// GET /users?sort=-created_at,name (descending created_at, ascending name)

Custom Sorts

Implement Spatie\QueryBuilder\Sorts\Sort:

use Spatie\QueryBuilder\Sorts\Sort;
use Illuminate\Database\Eloquent\Builder;
use Spatie\QueryBuilder\Enums\SortDirection;

class SortMostPopular implements Sort
{
    public function __invoke(Builder $query, SortDirection $direction, string $property): void
    {
        $query->withCount('followers')->orderBy('followers_count', $direction->value);
    }
}

Including Relationships

use Spatie\QueryBuilder\AllowedInclude;

QueryBuilder::for(User::class)
    ->allowedIncludes(
        'posts',                                     // Eager loads posts (also allows postsCount and postsExists)
        'posts.comments',                            // Nested eager loading
        AllowedInclude::count('commentsCount'),      // Only withCount, no full relation
        AllowedInclude::exists('postsExists'),       // Only withExists
        AllowedInclude::relationship('profile', 'userProfile'), // Alias
        AllowedInclude::callback('latestPost', fn ($query) => $query->latestOfMany()),
    );

// GET /users?include=posts,commentsCount,postsExists

Aggregate Includes

AllowedInclude::min('postsViewsMin', 'posts', 'views');   // withMin('posts', 'views')
AllowedInclude::max('postsViewsMax', 'posts', 'views');   // withMax('posts', 'views')
AllowedInclude::sum('postsViewsSum', 'posts', 'views');   // withSum('posts', 'views')
AllowedInclude::avg('postsViewsAvg', 'posts', 'views');   // withAvg('posts', 'views')

// GET /users?include=postsViewsSum,postsViewsAvg

Custom Includes

Implement Spatie\QueryBuilder\Includes\IncludeInterface:

use Spatie\QueryBuilder\Includes\IncludeInterface;
use Illuminate\Database\Eloquent\Builder;

class IncludeLatestPost implements IncludeInterface
{
    public function __invoke(Builder $query, string $include): void
    {
        $query->with(['latestPost' => fn ($q) => $q->latest()]);
    }
}

AllowedInclude::custom('latestPost', new IncludeLatestPost());

Field Selection

// GET /users?fields[users]=id,name&fields[posts]=id,title&include=posts
QueryBuilder::for(User::class)
    ->allowedFields('id', 'name', 'email')
    ->allowedIncludes('posts')
    ->get();

Configuration

Published to config/query-builder.php:

return [
    // Custom query parameter names
    'parameters' => [
        'include' => 'include',
        'filter' => 'filter',
        'sort' => 'sort',
        'fields' => 'fields',
    ],

    // Array value delimiter
    'delimiter' => ',',

    // Include suffixes (for count, exists, and aggregate includes)
    'suffixes' => [
        'count' => 'Count',
        'exists' => 'Exists',
        'min' => 'Min',
        'max' => 'Max',
        'sum' => 'Sum',
        'avg' => 'Avg',
    ],

    // Disable exception throwing for invalid queries
    'disable_invalid_filter_query_exception' => false,
    'disable_invalid_sort_query_exception' => false,
    'disable_invalid_include_query_exception' => false,
];

Wildcard Allow-All

Allow any requested filter, sort, or include without explicit listing. Restricted to local and testing environments:

QueryBuilder::for(User::class)
    ->allowedFilters('*')
    ->allowedSorts('*')
    ->allowedIncludes('*');

Starting from Existing Queries

// From an Eloquent query
QueryBuilder::for(User::where('active', true))
    ->allowedFilters('name')
    ->get();

// From a relation
QueryBuilder::for($team->users())
    ->allowedFilters('name')
    ->get();

Common Patterns

Controller with Full Query Builder

class UsersController
{
    public function index()
    {
        $users = QueryBuilder::for(User::class)
            ->allowedFilters(
                AllowedFilter::partial('name'),
                AllowedFilter::exact('email'),
                AllowedFilter::scope('active'),
            )
            ->allowedSorts('name', 'created_at')
            ->allowedIncludes('posts', 'permissions')
            ->allowedFields('id', 'name', 'email')
            ->defaultSort('name')
            ->paginate();

        return UserResource::collection($users);
    }
}

Dynamic Arrays with Variadic Methods

$filters = ['name', 'email'];
$sorts = ['name', 'created_at'];

QueryBuilder::for(User::class)
    ->allowedFilters(...$filters)
    ->allowedSorts(...$sorts);

Common Pitfalls

• N+1 queries: Always use allowedIncludes() to eager load relationships instead of accessing them in views/resources without loading
• Forgetting to allow: All filters, sorts, includes, and fields must be explicitly allowed. Unallowed parameters throw exceptions by default
• Filter value types: Filter values come from query strings as strings. Use AllowedFilter::exact() for boolean/integer columns, or handle casting in custom filters
• Nested include counts: Count and exists variants are only auto-generated for top-level includes, not for nested includes like posts.comments
• Field selection with includes: When using allowedFields() with allowedIncludes(), make sure to include the foreign key columns needed for the relationships

Source: spatie/laravel-query-builder — distributed by TomeVault.