laravel-query-builder Documentation

Easily build Eloquent queries from API requests

4,472 stars PHP PHP 1 file · ~12,600 tokens #api#hacktoberfest#laravel#php
RAW Doc

Technical Documentation: spatie/laravel-query-builder

ℹ️ Provenance: Multi-Tier Ingestion · CodeWiki Reference

Overview (`README.md`)

text
<a href="https://spatie.be/open-source?utm_source=github&utm_medium=banner&utm_campaign=laravel-query-builder">
  
    
    
  
</a>

Build Eloquent queries from API requests

Basic usage

Filter a query based on a request: `/users?filter[name]=John`:

php
use Spatie\QueryBuilder\QueryBuilder;

$users = QueryBuilder::for(User::class)
    ->allowedFilters('name')
    ->get();

// all `User`s that contain the string "John" in their name

Read more about filtering features like: partial filters, exact filters, scope filters, custom filters, ignored values, default filter values, ...

Grouping multiple filters with OR/AND: `/users?filter[q]=John`:

php
use Spatie\QueryBuilder\AllowedFilter;
use Spatie\QueryBuilder\QueryBuilder;

$users = QueryBuilder::for(User::class)
    ->allowedFilters(
        AllowedFilter::partial('name'),
        AllowedFilter::partial('full_name'),
        AllowedFilter::groupOr('q', [
            AllowedFilter::partial('name'),
            AllowedFilter::partial('full_name'),
        ]),
    )
    ->get();

// /users?filter[q]=John
//   → WHERE (name LIKE '%John%' OR full_name LIKE '%John%')
//
// /users?filter[q]=John&filter[name]=Doe
//   → WHERE name LIKE '%Doe%' AND (name LIKE '%John%' OR full_name LIKE '%John%')

AllowedFilter::groupAnd() is also available. Members can be any AllowedFilter type and the shorthand value is broadcast to every member.

Read more about the JSON:API Fancy Filters recommendation this feature follows.

Including relations based on a request: `/users?include=posts`:

php
$users = QueryBuilder::for(User::class)
    ->allowedIncludes('posts')
    ->get();

// all `User`s with their `posts` loaded

Read more about include features like: including nested relationships, including relationship count, custom includes, ...

Sorting a query based on a request: `/users?sort=id`:

php
$users = QueryBuilder::for(User::class)
    ->allowedSorts('id')
    ->get();

// all `User`s sorted by ascending id

Read more about sorting features like: custom sorts, sort direction, ...

Works together nicely with existing queries:

php
$query = User::where('active', true);

$userQuery = QueryBuilder::for($query) // start from an existing Builder instance
    ->withTrashed() // use your existing scopes
    ->allowedIncludes('posts', 'permissions')
    ->where('score', '>', 42); // chain on any of Laravel's query builder methods

Selecting fields for a query: `/users?fields[users]=id,email`

php
$users = QueryBuilder::for(User::class)
    ->allowedFields('id', 'email')
    ->get();

// the fetched `User`s will only have their id & email set

Read more about selecting fields.

Support us

We invest a lot of resources into creating best in class open source packages. You can support us by buying one of our paid products.

We highly appreciate you sending us a postcard from your hometown, mentioning which of our package(s) you are using. You'll find our address on our contact page. We publish all received postcards on our virtual postcard wall.

Installation

You can install the package via composer:

bash
composer require spatie/laravel-query-builder

Read the installation notes on the docs site: https://spatie.be/docs/laravel-query-builder/v7/installation-setup.

Documentation

You can find the documentation on https://spatie.be/docs/laravel-query-builder/v7.

Find yourself stuck using the package? Found a bug? Do you have general questions or suggestions for improving the media library? Feel free to create an issue on GitHub, we'll try to address it as soon as possible.

If you've found a bug regarding security please mail [email protected] instead of using the issue tracker.

Upgrading

Please see UPGRADING.md for details.

Testing

bash
composer test

Changelog

Please see CHANGELOG for more information what has changed recently.

Contributing

Please see CONTRIBUTING for details.

Security

If you've found a bug regarding security please mail [email protected] instead of using the issue tracker.

Credits

License

The MIT License (MIT). Please see License File for more information.

Index (`docs/_index.md`)

title: v7 slogan: Easily build Eloquent queries from API requests. githubUrl: https://github.com/spatie/laravel-query-builder branch: main

About Us (`docs/about-us.md`)

title: About us weight: 4

Spatie is a webdesign agency based in Antwerp, Belgium.

Open source software is used in all projects we deliver. Laravel, Nginx, Ubuntu are just a few
of the free pieces of software we use every single day. For this, we are very grateful.
When we feel we have solved a problem in a way that can help other developers,
we release our code as open source software on GitHub.

Changelog (`docs/changelog.md`)

title: Changelog weight: 6

All notable changes to laravel-query-builder are documented on GitHub

Installation Setup (`docs/installation-setup.md`)

title: Installation & setup weight: 4

You can install the package via composer:

bash
composer require spatie/laravel-query-builder

The package will automatically register its service provider.

You can optionally publish the config file with:

bash
php artisan vendor:publish --provider="Spatie\QueryBuilder\QueryBuilderServiceProvider" --tag="query-builder-config"

These are the contents of the default config file that will be published:

text
/* Detailed source-code truncated for AI context efficiency. */

Introduction (`docs/introduction.md`)

title: Introduction weight: 1

This package allows you to filter, sort and include eloquent relations based on a request. The QueryBuilder used in this package extends Laravel's default Eloquent builder. This means all your favorite methods and macros are still available. Query parameter names follow the JSON API specification as closely as possible.

Here's how we use the package ourselves in Mailcoach.

Basic usage

Filter a query based on a request: `/users?filter[name]=John`:

php
use Spatie\QueryBuilder\QueryBuilder;

$users = QueryBuilder::for(User::class)
    ->allowedFilters('name')
    ->get();

// all `User`s that contain the string "John" in their name

Read more about filtering features like: partial filters, exact filters, scope filters, custom filters, ignored values, default filter values, ...

Including relations based on a request: `/users?include=posts`:

php
$users = QueryBuilder::for(User::class)
    ->allowedIncludes('posts')
    ->get();

// all `User`s with their `posts` loaded

Read more about include features like: including nested relationships, including relationship count, ...

Sorting a query based on a request: `/users?sort=id`:

php
$users = QueryBuilder::for(User::class)
    ->allowedSorts('id')
    ->get();

// all `User`s sorted by ascending id

Read more about sorting features like: custom sorts, sort direction, ...

Works together nicely with existing queries:

php
$query = User::where('active', true);

$userQuery = QueryBuilder::for($query) // start from an existing Builder instance
    ->withTrashed() // use your existing scopes
    ->allowedIncludes('posts', 'permissions')
    ->where('score', '>', 42); // chain on any of Laravel's query builder methods

Selecting fields for a query: `/users?fields=id,email`

php
$users = QueryBuilder::for(User::class)
    ->allowedFields('id', 'email')
    ->get();

// the fetched `User`s will only have their id & email set

Read more about selecting fields.

We have badges!

Questions Issues (`docs/questions-issues.md`)

title: Questions and issues weight: 5

Find yourself stuck using the package? Found a bug? Do you have general questions or suggestions for improving the Laravel query builder? Feel free to create an issue on GitHub, we'll try to address it as soon as possible.

If you've found a bug regarding security please mail [email protected] instead of using the issue tracker.

Requirements (`docs/requirements.md`)

title: Requirements weight: 3

The Query Builder package requires PHP 8.3 or above and Laravel 12 or above.

We only support and maintain the latest version. If you do not meet the minimum requirements, you can opt to use an older version of the package.

Support Us (`docs/support-us.md`)

title: Support us weight: 4

We invest a lot of resources into creating best in class open source packages. You can support us by buying one of our paid products.

We highly appreciate you sending us a postcard from your hometown, mentioning which of our package(s) you are using. You'll find our address on our contact page. We publish all received postcards on our virtual postcard wall.

Advanced Usage Extending Query Builder (`docs/advanced-usage/extending-query-builder.md`)

title: Extending query builder weight: 1

As the QueryBuilder extends Laravel's default Eloquent query builder you can use any method or macro you like. You can also specify a base query instead of the model FQCN:

php
QueryBuilder::for(User::where('id', 42)) // base query instead of model
    ->allowedIncludes('posts')
    ->where('activated', true) // chain on any of Laravel's query methods
    ->first(); // we only need one specific user

Advanced Usage Front End Implementation (`docs/advanced-usage/front-end-implementation.md`)

title: Front-end implementation weight: 6

If you're interested in building query urls on the front-end to match this package, you could use one of the below:

Advanced Usage Multi Value Delimiter (`docs/advanced-usage/multi-value-delimiter.md`)

title: Multi value delimiter weight: 4

Sometimes values to filter for could include commas. You can change the delimiter used to split array values by setting the delimiter key in the query-builder config file.

php
// config/query-builder.php

return [
    'delimiter' => '|',
];

With this configuration, a request like GET /api/endpoint?filter[voltage]=12,4V|4,7V|2,1V would be parsed as:

php
// filters: [ 'voltage' => [ '12,4V', '4,7V', '2,1V' ]]

Note that this applies to ALL values for filters, includes and sorts.

To disable splitting entirely for all parameters, set the global delimiter to an empty string:

php
// config/query-builder.php

return [
    'delimiter' => '',
];

If you need only filter values to stay intact globally, you can disable filter delimiter splitting in config:

php
// config/query-builder.php

'filter_value_splitting_enabled' => false,

This only affects filter values. Includes, sorts, fields, and appends will continue using the configured global delimiter. The default value is true.

Per filter delimiter

You can override the delimiter for a specific filter using the delimiter() method. This is useful when a filter value may contain the default delimiter character.

php
// GET /api/endpoint?filter[voltage]=12,4V|4,7V|2,1V&filter[name]=John,Jane

QueryBuilder::for(Model::class)
    ->allowedFilters(
        AllowedFilter::exact('voltage')->delimiter('|'),
        AllowedFilter::exact('name'), // still uses the default comma delimiter
    )
    ->get();

To disable splitting entirely for a filter, set the delimiter to an empty string:

php
AllowedFilter::exact('external_id')->delimiter('')

Advanced Usage Pagination (`docs/advanced-usage/pagination.md`)

title: Pagination weight: 2

This package doesn't provide any methods to help you paginate responses. However as documented above you can use Laravel's default paginate() method.

If you want to completely adhere to the JSON API specification you can also use our own spatie/json-api-paginate!

Adding Parameters to Pagination

By default the query parameters wont be added to the pagination json. You can append the request query to the pagination json by using the appends method available on the LengthAwarePaginator.

php
$users = QueryBuilder::for(User::class)
    ->allowedFilters('name', 'email')
    ->paginate()
    ->appends(request()->query());

Features Filtering (`docs/features/filtering.md`)

title: Filtering weight: 1

The filter query parameters can be used to add where clauses to your Eloquent query. Out of the box we support filtering results by partial attribute value, exact attribute value or even if an attribute value exists in a given array of values. For anything more advanced, custom filters can be used.

By default, all filters have to be explicitly allowed using allowedFilters(). This method takes strings or AllowedFilter instances as arguments. An allowed filter can be partial, beginsWith, endsWith, exact, scope or custom. By default, any string values passed to allowedFilters() will automatically be converted to AllowedFilter::partial() filters.

Basic usage

php
// GET /users?filter[name]=john&filter[email]=gmail

$users = QueryBuilder::for(User::class)
    ->allowedFilters('name', 'email')
    ->get();

// $users will contain all users with "john" in their name AND "gmail" in their email address

You can specify multiple matching filter values by passing a comma separated list of values:

php
// GET /users?filter[name]=seb,freek

$users = QueryBuilder::for(User::class)
    ->allowedFilters('name')
    ->get();

// $users will contain all users that contain "seb" OR "freek" in their name

By passing column name strings to allowedFilters, partial filters are automatically applied.

Disallowed filters

Finally, when trying to filter on properties that have not been allowed using allowedFilters() an InvalidFilterQuery exception will be thrown along with a list of allowed filters.

Disable InvalidFilterQuery exception

You can set in configuration file to not throw an InvalidFilterQuery exception when a filter is not set in allowedFilter method. This does not allow using any filter, it just disables the exception.

php
'disable_invalid_filter_query_exception' => true

By default the option is set false.

Partial, beginsWith and endsWith filters

By default, all values passed to allowedFilters are converted to partial filters. The underlying query will be modified to use a LIKE LOWER(%value%) statement. Because this can cause missed indexes, it's often worth considering a beginsWith filter for the beginning of the value, or an endsWith filter for the end of the value. These filters will use a LIKE value% statement and a LIKE %value statement respectively, instead of the default partial filter. This can help optimize query performance and index utilization.

Exact filters

When filtering IDs, boolean values or a literal string, you'll want to use exact filters. This way /users?filter[id]=1 won't match all users having the digit 1 in their ID.

Exact filters can be added using Spatie\QueryBuilder\AllowedFilter::exact('property_name') in the allowedFilters() method.

php
use Spatie\QueryBuilder\AllowedFilter;

// GET /users?filter[name]=John%20Doe
$users = QueryBuilder::for(User::class)
    ->allowedFilters(AllowedFilter::exact('name'))
    ->get();

// only users with the exact name "John Doe"

The query builder will automatically map 1, 0, 'true', and 'false' as boolean values and a comma separated list of values as an array:

php
use Spatie\QueryBuilder\AllowedFilter;

// GET /users?filter[id]=1,2,3,4,5&filter[admin]=true

$users = QueryBuilder::for(User::class)
    ->allowedFilters(
        AllowedFilter::exact('id'),
        AllowedFilter::exact('admin'),
    )
    ->get();

// $users will contain all admin users with id 1, 2, 3, 4 or 5

Operator filters

Operator filters allow you to filter results based on different operators such as EQUAL, NOT_EQUAL, GREATER_THAN, LESS_THAN, GREATER_THAN_OR_EQUAL, LESS_THAN_OR_EQUAL, and DYNAMIC. You can use the AllowedFilter::operator method to create operator filters.

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

// GET /users?filter[salary]=3000
$users = QueryBuilder::for(User::class)
    ->allowedFilters(
        AllowedFilter::operator('salary', FilterOperator::GREATER_THAN),
    )
    ->get();

// $users will contain all users with a salary greater than 3000

You can also use dynamic operator filters, which allow you to specify the operator in the filter value:

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

// GET /users?filter[salary]=>3000
$users = QueryBuilder::for(User::class)
    ->allowedFilters(
        AllowedFilter::operator('salary', FilterOperator::DYNAMIC),
    )
    ->get();

// $users will contain all users with a salary greater than 3000

You can also add filters for a relationship property using the dot-notation: AllowedFilter::exact('posts.title'). This works for exact and partial filters. Under the hood we'll add a whereHas statement for the posts that filters for the given title property as well.

In some cases you'll want to disable this behaviour and just pass the raw filter-property value to the query. For example, when using a joined table's value for filtering. By passing false as the third parameter to AllowedFilter::exact() or AllowedFilter::partial() this behaviour can be disabled:

php
$addRelationConstraint = false;

QueryBuilder::for(User::class)
    ->join('posts', 'posts.user_id', 'users.id')
    ->allowedFilters(AllowedFilter::exact('posts.title', null, $addRelationConstraint));

BelongsTo filters

In Model:

php
class Comment extends Model
{
    public function post(): BelongsTo
    {
        return $this->belongsTo(Post::class);
    }
}
php
QueryBuilder::for(Comment::class)
    ->allowedFilters(
        AllowedFilter::belongsTo('post'),
    )
    ->get();

Alias

php
QueryBuilder::for(Comment::class)
    ->allowedFilters(
        AllowedFilter::belongsTo('post_id', 'post'),
    )
    ->get();

Nested

php
class Post extends Model
{
    public function author(): BelongsTo
    {
        return $this->belongsTo(User::class);
    }
}
php
QueryBuilder::for(Comment::class)
    ->allowedFilters(
        AllowedFilter::belongsTo('author_post_id', 'post.author'),
    )
    ->get();

Scope filters

Sometimes more advanced filtering options are necessary. This is where scope filters, callback filters and custom filters come in handy.

Scope filters allow you to add local scopes to your query by adding filters to the URL. This works for scopes on the queried model or its relationships using dot-notation.

Consider the following scope on your model:

php
public function scopeStartsBefore(Builder $query, $date): Builder
{
    return $query->where('starts_at', '<=', Carbon::parse($date));
}

To filter based on the startsBefore scope, add it to the allowedFilters array on the query builder:

php
QueryBuilder::for(Event::class)
    ->allowedFilters(
        AllowedFilter::scope('starts_before'),
    )
    ->get();

The following filter will now add the startsBefore scope to the underlying query:

text
GET /events?filter[starts_before]=2018-01-01

You can even pass multiple parameters to the scope by passing a comma separated list to the filter and use dot-notation for querying scopes on a relationship:

text
GET /events?filter[schedule.starts_between]=2018-01-01,2018-12-31

When passing an array as a parameter you can access it, as an array, in the scope by using the spread operator.

php
public function scopeInvitedUsers(Builder $query,  ...$users): Builder
{
    return $query->whereIn('id', $users);
}

When using scopes that require model instances in the parameters, we'll automatically try to inject the model instances into your scope. This works the same way as route model binding does for injecting Eloquent models into controllers. For example:

php
public function scopeEvent(Builder $query, \App\Models\Event $event): Builder
{
    return $query->where('id', $event->id);
}

// GET /events?filter[event]=1 - the event with ID 1 will automatically be resolved and passed to the scoped filter

If you use any other column aside id column for route model binding (ULID,UUID). Remember to specify the value of the column used in route model binding

php
// GET /events?filter[event]=01j0rcpkx5517v0aqyez5vnwn - supposing we use a ULID column for route model binding.

Scopes are usually not named with query filters in mind. Use filter aliases to alias them to something more appropriate:

php
QueryBuilder::for(User::class)
    ->allowedFilters(
        AllowedFilter::scope('unconfirmed', 'whereHasUnconfirmedEmail'),
        // `?filter[unconfirmed]=1` will now add the `scopeWhereHasUnconfirmedEmail` to your query
    );

Trashed filters

When using Laravel's soft delete feature you can use the AllowedFilter::trashed() filter to query these models.

The FiltersTrashed filter responds to particular values:

  • with: include soft-deleted records to the result set
  • only: return only 'trashed' records at the result set
  • any other value: return only records without that are not soft-deleted in the result set

For example:

php
QueryBuilder::for(Booking::class)
    ->allowedFilters(
        AllowedFilter::trashed(),
    );

// GET /bookings?filter[trashed]=only will only return soft deleted models

Callback filters

If you want to define a tiny custom filter, you can use a callback filter. Using AllowedFilter::callback(string $name, callable $filter) you can specify a callable that will be executed when the filter is requested.

The filter callback will receive the following parameters: Builder $query, mixed $value, string $name. You can modify the Builder object to add your own query constraints.

For example:

php
QueryBuilder::for(User::class)
    ->allowedFilters(
        AllowedFilter::callback('has_posts', function (Builder $query, $value) {
            $query->whereHas('posts');
        }),
    );

Using PHP 7.4 this example becomes a lot shorter:

php
QueryBuilder::for(User::class)
    ->allowedFilters(
        AllowedFilter::callback('has_posts', fn (Builder $query) => $query->whereHas('posts')),
    );

Custom filters

You can specify custom filters using the AllowedFilter::custom() method. Custom filters are instances of invokable classes that implement the \Spatie\QueryBuilder\Filters\Filter interface. The __invoke method will receive the current query builder instance and the filter name/value. This way you can build any query your heart desires.

For example:

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

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

// In your controller for the following request:
// GET /users?filter[permission]=createPosts

$users = QueryBuilder::for(User::class)
    ->allowedFilters(
        AllowedFilter::custom('permission', new FiltersUserPermission),
    )
    ->get();

// $users will contain all users that have the `createPosts` permission

The @implements annotation is only there for static analysis. Use Filter<Model> for a filter that works on any model, or name a concrete model (Filter) when the filter is written for one. The same goes for the Sort and IncludeInterface interfaces.

Filter aliases

It can be useful to specify an alias for a filter to avoid exposing database column names. For example, your users table might have a user_passport_full_name column, which is a horrible name for a filter. Using aliases you can specify a new, shorter name for this filter:

php
use Spatie\QueryBuilder\AllowedFilter;

// GET /users?filter[name]=John

$users = QueryBuilder::for(User::class)
    ->allowedFilters(AllowedFilter::exact('name', 'user_passport_full_name')) // will filter by the `user_passport_full_name` column
    ->get();

Ignored filters values

You can specify a set of ignored values for every filter. This allows you to not apply a filter when these values are submitted.

php
QueryBuilder::for(User::class)
    ->allowedFilters(
        AllowedFilter::exact('name')->ignore(null),
    )
    ->get();

The ignore() method takes one or more values, where each may be an array of ignored values. Each of the following calls are valid:

  • ignore('should_be_ignored')
  • ignore(null, '-1')
  • ignore([null, 'ignore_me', 'also_ignored'])

Given an array of values to filter for, only the subset of non-ignored values get passed to the filter. If all values are ignored, the filter does not get applied.

php
// GET /user?filter[name]=forbidden,John%20Doe

QueryBuilder::for(User::class)
    ->allowedFilters(
        AllowedFilter::exact('name')->ignore('forbidden'),
    )
    ->get();
// Returns only users where name matches 'John Doe'

// GET /user?filter[name]=ignored,ignored_too

QueryBuilder::for(User::class)
    ->allowedFilters(
        AllowedFilter::exact('name')->ignore(['ignored', 'ignored_too']),
    )
    ->get();
// Filter does not get applied because all requested values are ignored.

Default Filter Values

You can specify a default value for a filter if a value for the filter was not present on the request. This is especially useful for boolean filters.

php
QueryBuilder::for(User::class)
    ->allowedFilters(
        AllowedFilter::exact('name')->default('Joe'),
        AllowedFilter::scope('deleted')->default(false),
        AllowedFilter::scope('permission')->default(null),
    )
    ->get();

Nullable Filter

You can mark a filter nullable if you want to retrieve entries whose filtered value is null. This way you can apply the filter with an empty value, as shown in the example.

php
// GET /user?filter[name]=&filter[permission]=

QueryBuilder::for(User::class)
    ->allowedFilters(
        AllowedFilter::exact('name')->nullable(),
        AllowedFilter::scope('permission')->nullable(),
    )
    ->get();

Filter groups (OR / AND conjunction)

Group multiple filters under a single URL parameter with an explicit conjunction.
Anchored on the JSON:API Fancy Filters
extension recommendation; the JSON:API base spec's filter parameter family is
intentionally extension-friendly.

Basic usage

php
use Spatie\QueryBuilder\AllowedFilter;

QueryBuilder::for(User::class)
    ->allowedFilters(
        AllowedFilter::groupOr('q', [
            AllowedFilter::partial('name'),
            AllowedFilter::partial('full_name'),
        ]),
    );

/users?filter[q]=John →

sql
WHERE (LOWER(name) LIKE LOWER('%John%') OR LOWER(full_name) LIKE LOWER('%John%'))

`groupAnd`

php
AllowedFilter::groupAnd('match', [
    AllowedFilter::partial('name'),
    AllowedFilter::partial('full_name'),
]);

/users?filter[match]=John →

sql
WHERE (LOWER(name) LIKE LOWER('%John%') AND LOWER(full_name) LIKE LOWER('%John%'))

Multiple independent groups

Groups are AND-joined to each other:

php
->allowedFilters(
    AllowedFilter::groupOr('q', [
        AllowedFilter::partial('name'),
        AllowedFilter::partial('full_name'),
    ]),
    AllowedFilter::groupOr('loc', [
        AllowedFilter::partial('city'),
        AllowedFilter::partial('country'),
    ]),
)

/users?filter[q]=John&filter[loc]=ist →

sql
WHERE (LOWER(name) LIKE LOWER('%John%') OR LOWER(full_name) LIKE LOWER('%John%'))
  AND (LOWER(city) LIKE LOWER('%ist%') OR LOWER(country) LIKE LOWER('%ist%'))

Combining with top-level filters

If a member's name is also registered as a top-level filter, both apply (AND-joined):

php
->allowedFilters(
    AllowedFilter::partial('name'),
    AllowedFilter::groupOr('q', [
        AllowedFilter::partial('name'),
        AllowedFilter::partial('full_name'),
    ]),
)

/users?filter[name]=Ali&filter[q]=John →

sql
WHERE LOWER(name) LIKE LOWER('%Ali%')
  AND (LOWER(name) LIKE LOWER('%John%') OR LOWER(full_name) LIKE LOWER('%John%'))

Mixed member types

Members can be any filter type. The shorthand value is broadcast to every member as-is:

php
AllowedFilter::groupOr('q', [
    AllowedFilter::partial('name'),    // LIKE %value%
    AllowedFilter::exact('status'),    // = value
    AllowedFilter::scope('active'),    // User::active(value)
])

The same raw filter value is passed to every member; the caller must ensure it's meaningful for each filter type.
For boolean scopes or other type-specific filters, prefer registering them as separate top-level filters instead.

Validation

  • Members array must be non-empty.
  • Each member must be an AllowedFilter instance.
  • Conjunction must be 'or' or 'and'.

Any of these violations throws InvalidArgumentException at registration time, not at query execution.

Features Including Relationships (`docs/features/including-relationships.md`)

title: Including relationships weight: 3

The include query parameter will load any Eloquent relation or relation count on the resulting models.
All includes must be explicitly allowed using allowedIncludes(). This method takes relationship names or AllowedInclude instances as arguments.

Basic usage

php
// GET /users?include=posts

$users = QueryBuilder::for(User::class)
    ->allowedIncludes('posts')
    ->get();

// $users will have all their `posts()` related models loaded

You can load multiple relationships by separating them with a comma:

php
// GET /users?include=posts,permissions
$users = QueryBuilder::for(User::class)
    ->allowedIncludes('posts', 'permissions')
    ->get();

// $users will contain all users with their posts and permissions loaded

Default includes

There is no way to include relationships by default in this package. Default relationships are built-in to Laravel itself using the with() method on a query:

php
$users = QueryBuilder::for(User::class)
    ->allowedIncludes('friends')
    ->with('posts') // posts will always by included, friends can be requested
    ->withCount('posts')
    ->withExists('posts')
    ->get();

Disallowed includes

When trying to include relationships that have not been allowed using allowedIncludes() an InvalidIncludeQuery exception will be thrown. Its exception message contains the allowed includes for reference.

Nested relationships

You can load nested relationships using the dot . notation:

php
// GET /users?include=posts.comments,permissions

$users = QueryBuilder::for(User::class)
    ->allowedIncludes('posts.comments', 'permissions')
    ->get();

// $users will contain all users with their posts, comments on their posts and permissions loaded

Every allowed include will automatically allow requesting its related model count using a Count suffix. On top of that it's also possible to specifically allow requesting and querying the related model count (and not include the entire relationship).

Under the hood this uses Laravel's withCount method. Read more about the withCount method here.

php
// GET /users?include=postsCount,friendsCount

$users = QueryBuilder::for(User::class)
    ->allowedIncludes(
        'posts', // allows including `posts` or `postsCount` or `postsExists`
        AllowedInclude::count('friendsCount'), // only allows include the number of `friends()` related models
    );
// every user in $users will contain a `posts_count` and `friends_count` property

Every allowed include will automatically allow requesting its related model exists using a Exists suffix. On top of that it's also possible to specifically allow requesting and querying the related model exists (and not include the entire relationship).

Under the hood this uses Laravel's withExists method. Read more about the withExists method here.

php
// GET /users?include=postsExists,friendsExists

$users = QueryBuilder::for(User::class)
    ->allowedIncludes(
        'posts', // allows including `posts` or `postsCount` or `postsExists`
        AllowedInclude::exists('friendsExists'), // only allows include the existence of `friends()` related models
    );
// every user in $users will contain a `posts_exists` and `friends_exists` property

Including aggregate values (min, max, sum, avg)

You can include aggregate values for related models using AllowedInclude::min(), AllowedInclude::max(), AllowedInclude::sum(), and AllowedInclude::avg(). These correspond to Laravel's withMin(), withMax(), withSum(), and withAvg() methods.

Unlike count and exists includes, aggregate includes require you to specify both the relationship name and the column to aggregate. This means they cannot be auto-generated from strings and must be defined explicitly.

php
// GET /users?include=postsViewsSum,postsViewsAvg

$users = QueryBuilder::for(User::class)
    ->allowedIncludes(
        AllowedInclude::sum('postsViewsSum', 'posts', 'views'),
        AllowedInclude::avg('postsViewsAvg', 'posts', 'views'),
    )
    ->get();

// every user in $users will contain a `posts_sum_views` and `posts_avg_views` property

All four aggregate types work the same way:

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

The resulting attribute names follow Laravel's convention: {relation}_{function}_{column} (e.g. posts_sum_views).

The suffixes used for matching include names can be customized in the query-builder config file using the suffixes array.

Constraining aggregate includes

All aggregate includes (count, exists, min, max, sum, avg) accept an optional constraint closure. This allows you to filter which related models are included in the aggregate calculation.

php
use Spatie\QueryBuilder\AllowedInclude;
use Illuminate\Database\Eloquent\Builder;

$users = QueryBuilder::for(User::class)
    ->allowedIncludes(
        AllowedInclude::count('publishedPostsCount', 'posts', fn (Builder $query) => $query->where('published', true)),
        AllowedInclude::sum('publishedPostsViewsSum', 'posts', 'views', constraint: fn (Builder $query) => $query->where('published', true)),
    )
    ->get();

The constraint closure receives a Builder instance, allowing you to add any query conditions. This works the same way for all aggregate types:

php
AllowedInclude::count('name', 'relation', fn (Builder $query) => $query->where('active', true));
AllowedInclude::exists('name', 'relation', fn (Builder $query) => $query->where('active', true));
AllowedInclude::min('name', 'relation', 'column', constraint: fn (Builder $query) => $query->where('active', true));
AllowedInclude::max('name', 'relation', 'column', constraint: fn (Builder $query) => $query->where('active', true));
AllowedInclude::sum('name', 'relation', 'column', constraint: fn (Builder $query) => $query->where('active', true));
AllowedInclude::avg('name', 'relation', 'column', constraint: fn (Builder $query) => $query->where('active', true));

Note that for min, max, sum, and avg, the constraint must be passed as a named argument since $internalName comes before it in the method signature.

Include aliases

It can be useful to specify an alias for an include to enable friendly relationship names. For example, your users table might have a userProfile relationship, which might be neater just specified as profile. Using aliases you can specify a new, shorter name for this include:

php
use Spatie\QueryBuilder\AllowedInclude;

// GET /users?include=profile

$users = QueryBuilder::for(User::class)
    ->allowedIncludes(AllowedInclude::relationship('profile', 'userProfile')) // will include the `userProfile` relationship
    ->get();

Custom includes

You can specify custom includes using the AllowedInclude::custom() method. Custom includes are instances of invokable classes that implement the \Spatie\QueryBuilder\Includes\IncludeInterface interface. The __invoke method will receive the current query builder instance and the include name. This way you can build any query your heart desires.

For example:

php
use Spatie\QueryBuilder\Includes\IncludeInterface;
use Illuminate\Database\Eloquent\Builder;
use Illuminate\Database\Eloquent\Model;
use App\Models\Post;

/**
 * @implements IncludeInterface<Model>
 */
class AggregateInclude implements IncludeInterface
{
    protected string $column;

    protected string $function;

    public function __construct(string $column, string $function)
    {
        $this->column = $column;

        $this->function = $function;
    }

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

// In your controller for the following request:
// GET /posts?include=comments_sum_votes

$posts = QueryBuilder::for(Post::class)
    ->allowedIncludes(
        AllowedInclude::custom('comments_sum_votes', new AggregateInclude('votes', 'sum'), 'comments'),
    )
    ->get();

// every post in $posts will contain a `comments_sum_votes` property

Callback includes

If you want to define a tiny custom include, you can use a callback include. Using AllowedInclude::callback(string $name, Closure $callback, ?string $internalName = null) you can specify a Closure that will be executed when the includes is requested.

You can modify the Builder object to add your own query constraints.

For example:

php
QueryBuilder::for(User::class)
    ->allowedIncludes(
        AllowedInclude::callback('latest_post', function (Builder $query) {
            $query->latestOfMany();
        }),
    );

Selecting included fields

You can select only some fields to be included using the allowedFields method on the query builder.

Include casing

Relation/include names will be passed from request URL to the query directly. This means /users?include=blog-posts will try to load blog-posts relationship and /users?include=blogPosts will try to load the blogPosts() relationship.

Eloquent API resources

Once the relationships are included, we'd recommend including them in your response by using Eloquent API resources and conditional relationships.

Features Selecting Fields (`docs/features/selecting-fields.md`)

title: Selecting fields weight: 4

Sometimes you'll want to fetch only a couple fields to reduce the overall size of your SQL query. This can be done by specifying some fields using the allowedFields method and using the fields request query parameter.

Basic usage

The following example fetches only the users' id and name:

php
// GET /users?fields[users]=id,name

$users = QueryBuilder::for(User::class)
    ->allowedFields('id', 'name')
    ->toSql();

The SQL query will look like this:

sql
SELECT "id", "name" FROM "users"

When not allowing any fields explicitly, Eloquent's default behaviour of selecting all fields will be used.

Disallowed fields/selects

When trying to select a column that's not specified in allowedFields() an InvalidFieldQuery exception will be thrown:

php
$users = QueryBuilder::for(User::class)
    ->allowedFields('name')
    ->get();

// GET /users?fields[users]=email will throw an `InvalidFieldQuery` exception as `email` is not an allowed field.

Selecting fields for included relations

Selecting fields for included models works the same way. This is especially useful when you only need a couple of columns from an included relationship. Consider the following example:

php
GET /posts?include=author&fields[authors]=id,name

QueryBuilder::for(Post::class)
    ->allowedFields('authors.id', 'authors.name')
    ->allowedIncludes('author');

// All posts will be fetched including _only_ the name of the author.

⚠️ Note: In allowedFields, you must always use the snake case plural of your relation name. If you want to change this behavior, you can change the settings in the configuration file

⚠️ Keep in mind that the fields query will completely override the SELECT part of the query. This means that you'll need to manually specify any columns required for Eloquent relationships to work, in the above example author.id. See issue #175 as well.

Features Sorting (`docs/features/sorting.md`)

title: Sorting weight: 2

The sort query parameter is used to determine by which property the results collection will be ordered. Sorting is ascending by default and can be reversed by adding a hyphen (-) to the start of the property name.

All sorts have to be explicitly allowed using the allowedSorts() method. The allowedSorts method takes column names as strings or instances of AllowedSort.

For more advanced use cases, custom sorts can be used.

Basic usage

php
// GET /users?sort=-name

$users = QueryBuilder::for(User::class)
    ->allowedSorts('name')
    ->get();

// $users will be sorted by name and descending (Z -> A)

To define a default sort parameter that should be applied without explicitly adding it to the request, you can use the defaultSort method.

php
// GET /users
$users = QueryBuilder::for(User::class)
    ->defaultSort('name')
    ->allowedSorts('name', 'street')
    ->get();

// Will retrieve the users sorted by name

You can use - if you want to have the default order sorted descendingly.

php
// GET /users
$users = QueryBuilder::for(User::class)
    ->defaultSort('-name')
    ->allowedSorts('name', 'street')
    ->get();

// Will retrieve the users sorted descendingly by name

You can define multiple default sorts

php
// GET /users
$users = QueryBuilder::for(User::class)
    ->defaultSort('-street', 'name')
    ->allowedSorts('name', 'street')
    ->get();

// Will retrieve the users sorted descendingly by street than in ascending order by name

You can sort by multiple properties by separating them with a comma:

php
// GET /users?sort=name,-street

$users = QueryBuilder::for(User::class)
    ->allowedSorts('name', 'street')
    ->get();

// $users will be sorted by name in ascending order with a secondary sort on street in descending order.

Disallowed sorts

When trying to sort by a property that's not specified in allowedSorts() an InvalidSortQuery exception will be thrown.

php
// GET /users?sort=password
$users = QueryBuilder::for(User::class)
    ->allowedSorts('name')
    ->get();

// Will throw an `InvalidSortQuery` exception as `password` is not an allowed sorting property

Custom sorts

You can specify custom sorting methods using the AllowedSort::custom() method. Custom sorts are instances of invokable classes that implement the \Spatie\QueryBuilder\Sorts\Sort interface. The __invoke method will receive the current query builder instance, the direction to sort in and the sort's name. This way you can build any sorting query your heart desires.

For example sorting by string column length:

php
/**
 * @implements \Spatie\QueryBuilder\Sorts\Sort<\Illuminate\Database\Eloquent\Model>
 */
class StringLengthSort implements \Spatie\QueryBuilder\Sorts\Sort
{
    public function __invoke(Builder $query, bool $descending, string $property)
    {
        $direction = $descending ? 'DESC' : 'ASC';

        $query->orderByRaw("LENGTH(`{$property}`) {$direction}");
    }
}

The custom StringLengthSort sort class can then be used like this to sort by the length of the users.name column:

php
// GET /users?sort=name-length

$users = QueryBuilder::for(User::class)
    ->allowedSorts(
        AllowedSort::custom('name-length', new StringLengthSort(), 'name'),
    )
    ->get();

// The requested `name-length` sort alias will invoke `StringLengthSort` with the `name` column name.

To change the default direction of the a sort you can use defaultDirection :

php
$customSort = AllowedSort::custom('custom-sort', new SentSort())->defaultDirection(SortDirection::Descending);

$users = QueryBuilder::for(User::class)
            ->allowedSorts($customSort)
            ->defaultSort($customSort)
            ->get();

Using an alias for sorting

There may be occasions where it is not appropriate to expose the column name to the user.

Similar to using an alias when filtering, you can do this for sorts as well.

The column name can be passed as optional parameter and defaults to the property string.

php
// GET /users?sort=-street
 $users = QueryBuilder::for(User::class)
    ->allowedSorts(
        AllowedSort::field('street', 'actual_column_street'),
    )
    ->get();

--- METRICS ---

Explore Related Technologies & Topics

View All 9,111+ Repositories →

Discover similar high-velocity repositories, agent skills, and OpenAPI specifications across the ecosystem.

#api Browse api catalog → #hacktoberfest Browse hacktoberfest catalog → #laravel Browse laravel catalog → #php Browse php catalog → ⚡ MCP Servers Model Context Protocol → 🧠 Agent Skills Prompt & rule files → 🔌 OpenAPI Specs REST & Swagger schemas →