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

```php
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

```php
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

```php
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:

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

### Custom Column Names

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

### Custom Filters

Implement `Spatie\QueryBuilder\Filters\Filter`:

```php
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

```php
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`:

```php
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

```php
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

```php
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`:

```php
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

```php
// GET /users?fields[users]=id,name&fields[posts]=id,title&include=posts
QueryBuilder::for(User::c