Laravel Query Builder (spatie/laravel-query-builder)

Laravel Query Builder

https://github.com/spatie/laravel-query-builder
Admin
Build Eloquent queries from API requests, allowing filtering, including relations, sorting, and...

Tokens:24,770
Snippets:173
Trust Score:-
Update:3 months ago
Show doc for...
Context Summary (auto-generated)
Raw
# Laravel Query Builder

Laravel Query Builder is a PHP package that simplifies building Eloquent queries from API request parameters. Developed by Spatie, this package enables developers to create flexible, secure APIs by converting URL query parameters into database queries. It provides a clean interface for handling common API operations like filtering, sorting, including relationships, and selecting specific fields while maintaining security through explicit whitelisting of allowed operations.

The package seamlessly integrates with Laravel's Eloquent ORM and works with any Laravel application running PHP 8.2+ and Laravel 10-12. By using QueryBuilder instead of standard Eloquent queries, developers can create feature-rich APIs that support advanced querying capabilities directly from URL parameters, eliminating the need for custom controller logic for each query variation. The package automatically handles query parameter parsing, validation, and exception handling for unauthorized query attempts.

## API Reference and Code Examples

### Basic QueryBuilder Setup

Initialize a QueryBuilder instance for any Eloquent model to enable API-driven queries.

```php
use Spatie\QueryBuilder\QueryBuilder;
use App\Models\User;

// Basic setup - allows any standard Eloquent query chaining
$users = QueryBuilder::for(User::class)
    ->where('active', true)
    ->get();

// Start from an existing query
$query = User::where('verified', true);
$users = QueryBuilder::for($query)
    ->allowedFilters('name')
    ->get();

// Using with a custom request
$users = QueryBuilder::for(User::class, request())
    ->allowedFilters('email')
    ->get();
```

### Filtering Data - Partial Filters

Apply partial text matching filters to search within text fields using LIKE queries.

```php
use Spatie\QueryBuilder\QueryBuilder;

// GET /users?filter[name]=john&filter[email]=gmail
$users = QueryBuilder::for(User::class)
    ->allowedFilters(['name', 'email'])
    ->get();
// Returns all users with "john" in name AND "gmail" in email

// Multiple values with OR logic
// GET /users?filter[name]=john,jane
$users = QueryBuilder::for(User::class)
    ->allowedFilters(['name'])
    ->get();
// Returns users with "john" OR "jane" in their name
```

### Filtering Data - Exact Filters

Use exact matching for IDs, booleans, and precise string matches to avoid partial matching issues.

```php
use Spatie\QueryBuilder\AllowedFilter;

// GET /users?filter[id]=1,2,3&filter[admin]=true
$users = QueryBuilder::for(User::class)
    ->allowedFilters([
        AllowedFilter::exact('id'),
        AllowedFilter::exact('admin'),
        AllowedFilter::exact('email'),
    ])
    ->get();
// Returns admin users with ID 1, 2, or 3

// Filtering on relationships
// GET /users?filter[posts.published]=true
$users = QueryBuilder::for(User::class)
    ->allowedFilters([
        AllowedFilter::exact('posts.published'),
    ])
    ->get();
// Returns users who have published posts
```

### Filtering Data - Operator Filters

Apply comparison operators for numeric and date filtering operations.

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

// GET /products?filter[price]=100
$products = QueryBuilder::for(Product::class)
    ->allowedFilters([
        AllowedFilter::operator('price', FilterOperator::GREATER_THAN),
    ])
    ->get();
// Returns products with price > 100

// Dynamic operator in URL
// GET /products?filter[price]=>100&filter[stock]=<50
$products = QueryBuilder::for(Product::class)
    ->allowedFilters([
        AllowedFilter::operator('price', FilterOperator::DYNAMIC),
        AllowedFilter::operator('stock', FilterOperator::DYNAMIC),
    ])
    ->get();
// Supports: >, <, >=, <=, =, !=
```

### Filtering Data - Scope Filters

Leverage existing Eloquent model scopes for complex filtering logic.

```php
use Spatie\QueryBuilder\AllowedFilter;

// Model scope definition
class Event extends Model
{
    public function scopeStartsBefore(Builder $query, $date): Builder
    {
        return $query->where('starts_at', '<=', Carbon::parse($date));
    }

    public function scopeStartsBetween(Builder $query, $start, $end): Builder
    {
        return $query->whereBetween('starts_at', [$start, $end]);
    }
}

// GET /events?filter[starts_before]=2024-12-31
$events = QueryBuilder::for(Event::class)
    ->allowedFilters([
        AllowedFilter::scope('starts_before'),
    ])
    ->get();

// Multiple parameters with comma separation
// GET /events?filter[starts_between]=2024-01-01,2024-12-31
$events = QueryBuilder::for(Event::class)
    ->allowedFilters([
        AllowedFilter::scope('starts_between'),
    ])
    ->get();
```

### Filtering Data - Callback Filters

Create inline custom filters using closures for quick filtering logic.

```php
use Spatie\QueryBuilder\AllowedFilter;
use Illuminate\Database\Eloquent\Builder;

// GET /users?filter[has_posts]=1
$users = QueryBuilder::for(User::class)
    ->allowedFilters([
        AllowedFilter::callback('has_posts', function (Builder $query, $value) {
            if ($value) {
                $query->whereHas('posts');
            }
        }),
        AllowedFilter::callback('min_posts', fn (Builder $query, $value) =>
            $query->has('posts', '>=', $value)
        ),
    ])
    ->get();

// GET /users?filter[min_posts]=5
// Returns users with at least 5 posts
```

### Filtering Data - Custom Filter Classes

Implement reusable custom filters for complex or frequently used filtering logic.

```php
use Spatie\QueryBuilder\Filters\Filter;
use Illuminate\Database\Eloquent\Builder;
use Spatie\QueryBuilder\AllowedFilter;

class FiltersUserPermission implements Filter
{
    public function __invoke(Builder $query, $value, string $property)
    {
        $query->whereHas('permissions', function (Builder $query) use ($value) {
            $query->where('name', $value);
        });
    }
}

// GET /users?filter[permission]=create-posts
$users = QueryBuilder::for(User::class)
    ->allowedFilters([
        AllowedFilter::custom('permission', new FiltersUserPermission()),
    ])
    ->get();
// Returns users with 'create-posts' permission
```

### Filtering Data - Advanced Filter Options

Configure filters with default values, ignored values, nullable handling, and aliases.

```php
use Spatie\QueryBuilder\AllowedFilter;

// Default values
// GET /users (no filter parameter)
$users = QueryBuilder::for(User::class)
    ->allowedFilters([
        AllowedFilter::exact('status')->default('active'),
        AllowedFilter::exact('role')->default('user'),
    ])
    ->get();
// Applies status=active and role=user by default

// Ignored values
// GET /users?filter[status]=all,active,inactive
$users = QueryBuilder::for(User::class)
    ->allowedFilters([
        AllowedFilter::exact('status')->ignore('all', 'any'),
    ])
    ->get();
// Filters only by 'active' and 'inactive', ignores 'all'

// Nullable filters
// GET /users?filter[deleted_at]=
$users = QueryBuilder::for(User::class)
    ->allowedFilters([
        AllowedFilter::exact('deleted_at')->nullable(),
    ])
    ->get();
// Finds users where deleted_at IS NULL

// Filter aliases
// GET /users?filter[name]=John
$users = QueryBuilder::for(User::class)
    ->allowedFilters([
        AllowedFilter::exact('name', 'user_passport_full_name'),
    ])
    ->get();
// Filters by 'user_passport_full_name' column using 'name' parameter
```

### Filtering Data - Soft Delete Filters

Filter soft-deleted models using the trashed filter with multiple modes.

```php
use Spatie\QueryBuilder\AllowedFilter;

// GET /bookings?filter[trashed]=only
$bookings = QueryBuilder::for(Booking::class)
    ->allowedFilters([
        AllowedFilter::trashed(),
    ])
    ->get();
// Returns only soft-deleted bookings

// GET /bookings?filter[trashed]=with
// Returns all bookings including soft-deleted

// GET /bookings?filter[trashed]=without
// Returns only non-deleted bookings (default behavior)
```

### Sorting Data

Sort query results by one or multiple fields with ascending or descending order.

```php
use Spatie\QueryBuilder\QueryBuilder;
use Spatie\QueryBuilder\AllowedSort;

// GET /users?sort=name
$users = QueryBuilder::for(User::class)
    ->allowedSorts('name', 'email', 'created_at')
    ->get();
// Sorts by name ascending

// GET /users?sort=-created_at
// Sorts by created_at descending (hyphen prefix)

// Multiple sorts
// GET /users?sort=last_name,-created_at
$users = QueryBuilder::for(User::class)
    ->allowedSorts('last_name', 'created_at')
    ->get();
// Sorts by last_name ASC, then created_at DESC

// Default sort when no parameter provided
$users = QueryBuilder::for(User::class)
    ->defaultSort('-created_at', 'name')
    ->allowedSorts('name', 'created_at', 'email')
    ->get();
// Applies default sort if no ?sort= parameter in URL
```

### Sorting Data - Custom Sorts

Create custom sorting logic for complex ordering requirements.

```php
use Spatie\QueryBuilder\Sorts\Sort;
use Spatie\QueryBuilder\AllowedSort;
use Illuminate\Database\Eloquent\Builder;

class StringLengthSort implements Sort
{
    public function __invoke(Builder $query, bool $descending, string $property)
    {
        $direction = $descending ? 'DESC' : 'ASC';
        $query->orderByRaw("LENGTH(`{$property}`) {$direction}");
    }
}

// GET /users?sort=-name-length
$users = QueryBuilder::for(User::class)
    ->allowedSorts([
        AllowedSort::custom('name-length', new StringLengthSort(), 'name'),
    ])
    ->get();
// Sorts by name length descending

// Callback sort
// GET /users?sort=posts-count
$users = QueryBuilder::for(User::class)
    ->allowedSorts([
        AllowedSort::callback('posts-count', function (Builder $query, bool $desc) {
            $query->withCount('posts')->orderBy('posts_count', $desc ? 'desc' : 'asc');
        }),
    ])
    ->get();
```

### Sorting Data - Sort Aliases and Default Direction

Use aliases to hide database column names and set custom default sort directions.

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

// GET /users?sort=street
$users = QueryBuilder::for(User::class)
    ->allowedSorts([
        AllowedSort::field('street', 'actual_column_street_name'),
    ])
    ->get();
// Sorts by 'actual_column_street_name' using 'street' parameter

// Custom default direction
$customSort = AllowedSort::custom('priority', new CustomPrioritySort())
    ->defaultDirection(SortDirection::DESCENDING);

$tasks = QueryBuilder::for(Task::class)
    ->allowedSorts($customSort)
    ->defaultSort($customSort)
    ->get();
// Applies descending sort by default
```

### Including Relationships

Eager load Eloquent relationships based on URL parameters to reduce N+1 queries.

```php
use Spatie\QueryBuilder\QueryBuilder;

// GET /users?include=posts
$users = QueryBuilder::for(User::class)
    ->allowedIncludes(['posts'])
    ->get();
// Eager loads posts relationship

// Multiple includes
// GET /users?include=posts,permissions,profile
$users = QueryBuilder::for(User::class)
    ->allowedIncludes(['posts', 'permissions', 'profile'])
    ->get();

// Nested relationships
// GET /users?include=posts.comments,posts.author
$users = QueryBuilder::for(User::class)
    ->allowedIncludes(['posts.comments', 'posts.author'])
    ->get();

// Default includes (always loaded)
$users = QueryBuilder::for(User::class)
    ->allowedIncludes(['friends'])
    ->with('posts') // Always included regardless of URL
    ->withCount('followers')
    ->get();
```

### Including Relationship Counts and Exists

Include aggregated relationship data without loading full relationship models.

```php
use Spatie\QueryBuilder\AllowedInclude;

// GET /users?include=postsCount,friendsCount
$users = QueryBuilder::for(User::class)
    ->allowedIncludes([
        'posts', // Allows: posts, postsCount, postsExists
        AllowedInclude::count('friendsCount'), // Only count, not full relation
    ])
    ->get();
// Each user has posts_count and friends_count properties

// GET /users?include=postsExists,friendsExists
$users = QueryBuilder::for(User::class)
    ->allowedIncludes([
        'posts',
        AllowedInclude::exists('friendsExists'),
    ])
    ->get();
// Each user has posts_exists and friends_exists boolean properties
```

### Including Relationships - Custom Includes

Create custom include logic using callback or custom class implementations.

```php
use Spatie\QueryBuilder\AllowedInclude;
use Spatie\QueryBuilder\Includes\IncludeInterface;
use Illuminate\Database\Eloquent\Builder;

// Callback include
// GET /users?include=latest_post
$users = QueryBuilder::for(User::class)
    ->allowedIncludes([
        AllowedInclude::callback('latest_post', function (Builder $query) {
            $query->with(['posts' => fn($q) => $q->latest()->limit(1)]);
        }),
    ])
    ->get();

// Custom include class
class AggregateInclude implements IncludeInterface
{
    public function __construct(
        protected string $column,
        protected string $function
    ) {}

    public function __invoke(Builder $query, string $relations)
    {
        $query->withAggregate($relations, $this->column, $this->function);
    }
}

// GET /posts?include=comments_sum_votes
$posts = QueryBuilder::for(Post::class)
    ->allowedIncludes([
        AllowedInclude::custom(
            'comments_sum_votes',
            new AggregateInclude('votes', 'sum'),
            'comments'
        ),
    ])
    ->get();
// Each post has comments_sum_votes property
```

### Including Relationships - Aliases

Use friendly names for relationships in API URLs instead of database column names.

```php
use Spatie\QueryBuilder\AllowedInclude;

// GET /users?include=profile,history
$users = QueryBuilder::for(User::class)
    ->allowedIncludes([
        AllowedInclude::relationship('profile', 'userProfile'),
        AllowedInclude::relationship('history', 'orderHistory'),
    ])
    ->get();
// Loads userProfile and orderHistory relationships using cleaner names
```

### Selecting Fields

Optimize query performance by selecting only required columns from database tables.

```php
use Spatie\QueryBuilder\QueryBuilder;

// GET /users?fields[users]=id,name,email
$users = QueryBuilder::for(User::class)
    ->allowedFields(['id', 'name', 'email', 'created_at'])
    ->get();
// SQL: SELECT id, name, email FROM users

// Selecting fields for related models
// GET /posts?include=author&fields[posts]=id,title&fields[authors]=id,name
$posts = QueryBuilder::for(Post::class)
    ->allowedFields(['id', 'title', 'body', 'authors.id', 'authors.name'])
    ->allowedIncludes('author')
    ->get();
// Note: allowedFields must be called BEFORE allowedIncludes
// Use snake_case plural for relationship names by default

// Combining with includes and filters
// GET /users?include=posts&fields[users]=id,name&fields[posts]=id,title&filter[active]=true
$users = QueryBuilder::for(User::class)
    ->allowedFields(['id', 'name', 'email', 'posts.id', 'posts.title'])
    ->allowedIncludes('posts')
    ->allowedFilters(['active'])
    ->get();
```

### Configuration Options

Customize query parameter names, suffixes, and validation behavior globally.

```php
// config/query-builder.php

return [
    // Customize query parameter names
    'parameters' => [
        'include' => 'include',  // Change to 'with' if preferred
        'filter' => 'filter',    // Change to 'where' if preferred
        'sort' => 'sort',        // Change to 'order' if preferred
        'fields' => 'fields',    // Change to 'select' if preferred
        'append' => 'append',
    ],

    // Relationship count/exists suffixes
    'count_suffix' => 'Count',   // GET /users?include=postsCount
    'exists_suffix' => 'Exists', // GET /users?include=postsExists

    // Disable exceptions for invalid queries
    'disable_invalid_filter_query_exception' => false,
    'disable_invalid_sort_query_exception' => false,
    'disable_invalid_includes_query_exception' => false,

    // Relationship naming conventions
    'convert_relation_names_to_snake_case_plural' => true,
    // When true: fields[user_owners]=id,name
    // When false: fields[userOwner]=id,name

    'convert_field_names_to_snake_case' => false,
    // When true: converts ?fields=firstName to first_name
];
```

### Complete Example - Building a Flexible API Endpoint

Combine all features to create a powerful, flexible API endpoint with minimal code.

```php
use Spatie\QueryBuilder\QueryBuilder;
use Spatie\QueryBuilder\AllowedFilter;
use Spatie\QueryBuilder\AllowedSort;
use Spatie\QueryBuilder\AllowedInclude;
use Spatie\QueryBuilder\Enums\FilterOperator;

class UserController extends Controller
{
    public function index()
    {
        $users = QueryBuilder::for(User::class)
            // Filtering
            ->allowedFilters([
                AllowedFilter::partial('name'),
                AllowedFilter::partial('email'),
                AllowedFilter::exact('id'),
                AllowedFilter::exact('active')->default(true),
                AllowedFilter::exact('role'),
                AllowedFilter::scope('verified'),
                AllowedFilter::operator('created_at', FilterOperator::DYNAMIC),
                AllowedFilter::callback('has_posts',
                    fn($q) => $q->whereHas('posts')
                ),
                AllowedFilter::trashed(),
            ])
            // Sorting
            ->allowedSorts([
                'name',
                'email',
                'created_at',
                AllowedSort::custom('posts-count', new PostCountSort()),
            ])
            ->defaultSort('-created_at')
            // Selecting fields
            ->allowedFields([
                'id', 'name', 'email', 'role', 'created_at',
                'posts.id', 'posts.title', 'posts.created_at',
            ])
            // Including relationships
            ->allowedIncludes([
                'posts',
                'posts.comments',
                'permissions',
                AllowedInclude::count('postsCount'),
                AllowedInclude::callback('latest_post',
                    fn($q) => $q->with(['posts' => fn($q) => $q->latest()->limit(1)])
                ),
            ])
            ->paginate(15);

        return response()->json($users);
    }
}

// Example API calls:
// GET /users
// GET /users?filter[name]=john&filter[active]=true
// GET /users?filter[created_at]=>2024-01-01&sort=-created_at
// GET /users?include=posts,permissions&fields[users]=id,name,email
// GET /users?filter[role]=admin&include=postsCount&sort=name
// GET /users?filter[has_posts]=1&include=latest_post
```

## Summary and Integration

Laravel Query Builder streamlines the creation of sophisticated, query-driven APIs with minimal boilerplate code. Instead of writing separate controller methods for each query variation, developers define allowed operations once and let the package handle parameter parsing, validation, and query construction. The package supports all common API query patterns including text search, exact matching, relationship filtering, date ranges, and custom business logic through scopes and callbacks. Every operation requires explicit whitelisting through `allowedFilters()`, `allowedSorts()`, `allowedIncludes()`, and `allowedFields()` methods, ensuring security and preventing unauthorized database access.

Integration with existing Laravel applications is seamless - simply replace `Model::query()` or `Model::where()` calls with `QueryBuilder::for(Model::class)` in your controllers. The package works with all standard Eloquent features including soft deletes, global scopes, accessors, and relationships. It's particularly valuable for building JSON APIs, admin panels, data tables, and any interface requiring flexible data filtering and sorting. The configuration file allows customization of parameter names and validation behavior to match specific API design requirements. With automatic exception handling and detailed error messages, the package helps maintain clean, maintainable code while providing powerful query capabilities to API consumers.