](https://spatie.be/github-ad-click/laravel-query-builder)
We invest a lot of resources into creating [best in class open source packages](https://spatie.be/open-source). You can support us by [buying one of our paid products](https://spatie.be/open-source/support-us).
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](https://spatie.be/about-us). We publish all received postcards on [our virtual postcard wall](https://spatie.be/open-source/postcards).
## 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](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](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](https://github.com/spatie/laravel-query-builder/issues), we'll try to address it as soon as possible.
If you've found a bug regarding security please mail [security@spatie.be](mailto:security@spatie.be) instead of using the issue tracker.
### Upgrading
Please see [UPGRADING.md](UPGRADING.md) for details.
### Testing
```bash
composer test
```
### Changelog
Please see [CHANGELOG](CHANGELOG.md) for more information what has changed recently.
## Contributing
Please see [CONTRIBUTING](https://github.com/spatie/.github/blob/main/CONTRIBUTING.md) for details.
### Security
If you've found a bug regarding security please mail [security@spatie.be](mailto:security@spatie.be) instead of using the issue tracker.
## Credits
- [Alex Vanderbist](https://github.com/AlexVanderbist)
- [All Contributors](../../contributors)
## License
The MIT License (MIT). Please see [License File](LICENSE.md) 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](https://spatie.be) 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](https://spatie.be/opensource).
---
### Changelog (`docs/changelog.md`)
---
title: Changelog
weight: 6
---
All notable changes to laravel-query-builder are documented [on GitHub](https://github.com/spatie/laravel-query-builder/blob/master/CHANGELOG.md)
---
### 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:
```
/* 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](http://jsonapi.org/) as closely as possible.
Here's how we use the package ourselves in [Mailcoach](https://mailcoach.app).
## 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, ...](https://spatie.be/docs/laravel-query-builder/v7/features/filtering/)
### 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, ...](https://spatie.be/docs/laravel-query-builder/v7/features/including-relationships/)
### 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, ...](https://spatie.be/docs/laravel-query-builder/v7/features/sorting/)
### 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.](https://spatie.be/docs/laravel-query-builder/v7/features/selecting-fields/)
## We have badges!
[](https://packagist.org/packages/spatie/laravel-query-builder)
[](https://packagist.org/packages/spatie/laravel-query-builder)
---
### 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](https://github.com/spatie/laravel-query-builder/issues), we'll try to address it as soon as possible.
If you've found a bug regarding security please mail [freek@spatie.be](mailto:freek@spatie.be) 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](https://spatie.be/open-source). You can support us by [buying one of our paid products](https://spatie.be/open-source/support-us).
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](https://spatie.be/about-us). We publish all received postcards on [our virtual postcard wall](https://spatie.be/open-source/postcards).
---
### 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:
- Standalone: [elodo package](https://www.npmjs.com/package/elodo) by [Maxim Vanhove](https://github.com/MaximVanhove).
- Vue: [vue-api-query package](https://github.com/robsontenorio/vue-api-query) by [Robson Tenório](https://github.com/robsontenorio).
- Vue + Inertia.js: [inertiajs-tables-laravel-query-builder](https://github.com/protonemedia/inertiajs-tables-laravel-query-builder) by [
Pascal Baljet](https://github.com/pascalbaljet).
- React: [cogent-js package](https://www.npmjs.com/package/cogent-js) by [Joel Male](https://github.com/joelwmale).
- Typescript: [query-builder-ts package](https://www.npmjs.com/package/@vortechron/query-builder-ts) by [Amirul Adli](https://www.npmjs.com/~vortechron).
- Typescript + React [react-query-builder](https://www.npmjs.com/package/@cgarciagarcia/react-query-builder) by [Carlos Garcia](https://github.com/cgarciagarcia).
- Angular: [ng-qubee](https://www.npmjs.com/package/ng-qubee) by [Andrea](https://github.com/AndreaAlhena).
---
### 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](https://laravel.com/docs/12.x/pagination).
If you want to completely adhere to the JSON API specification you can also use our own [spatie/json-api-paginate](https://github.com/spatie/laravel-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](https://laravel.com/api/6.x/Illuminate/Contracts/Pagination/LengthAwarePaginator.html#method_appends).
```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
```
## Exact or partial filters for related properties
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](https://laravel.com/docs/master/eloquent#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:
```
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:
```
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](#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](https://laravel.com/docs/master/eloquent#querying-soft-deleted-models) 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