Filtering | laravel-query-builder | Spatie SPATIE Laravel Query Builder ======================== spatie.be/open-source [Docs](https://spatie.be/docs) [Laravel-query-builder](https://spatie.be/docs/laravel-query-builder/v7) Features Filtering Version v7 v6 v5 v4 v3 v2 Other versions for crawler [v7](https://spatie.be/docs/laravel-query-builder/v7) [v6](https://spatie.be/docs/laravel-query-builder/v6) [v5](https://spatie.be/docs/laravel-query-builder/v5) [v4](https://spatie.be/docs/laravel-query-builder/v4) [v3](https://spatie.be/docs/laravel-query-builder/v3) [v2](https://spatie.be/docs/laravel-query-builder/v2) Filtering - [ Introduction ](https://spatie.be/docs/laravel-query-builder/v7/introduction) - [ Requirements ](https://spatie.be/docs/laravel-query-builder/v7/requirements) - [ About us ](https://spatie.be/docs/laravel-query-builder/v7/about-us) - [ Installation & setup ](https://spatie.be/docs/laravel-query-builder/v7/installation-setup) - [ Support us ](https://spatie.be/docs/laravel-query-builder/v7/support-us) - [ Questions and issues ](https://spatie.be/docs/laravel-query-builder/v7/questions-issues) - [ Changelog ](https://spatie.be/docs/laravel-query-builder/v7/changelog) Features -------- - [ Filtering ](https://spatie.be/docs/laravel-query-builder/v7/features/filtering) - [ Sorting ](https://spatie.be/docs/laravel-query-builder/v7/features/sorting) - [ Including relationships ](https://spatie.be/docs/laravel-query-builder/v7/features/including-relationships) - [ Selecting fields ](https://spatie.be/docs/laravel-query-builder/v7/features/selecting-fields) Advanced usage -------------- - [ Extending query builder ](https://spatie.be/docs/laravel-query-builder/v7/advanced-usage/extending-query-builder) - [ Pagination ](https://spatie.be/docs/laravel-query-builder/v7/advanced-usage/pagination) - [ Multi value delimiter ](https://spatie.be/docs/laravel-query-builder/v7/advanced-usage/multi-value-delimiter) - [ Front-end implementation ](https://spatie.be/docs/laravel-query-builder/v7/advanced-usage/front-end-implementation) Filtering ========= ### On this page 1. [ Basic usage ](#content-basic-usage) 2. [ Disallowed filters ](#content-disallowed-filters) 3. [ Disable InvalidFilterQuery exception ](#content-disable-invalidfilterquery-exception) 4. [ Partial, beginsWith and endsWith filters ](#content-partial-beginswith-and-endswith-filters) 5. [ Exact filters ](#content-exact-filters) 6. [ Operator filters ](#content-operator-filters) 7. [ Exact or partial filters for related properties ](#content-exact-or-partial-filters-for-related-properties) 8. [ BelongsTo filters ](#content-belongsto-filters) 9. [ Scope filters ](#content-scope-filters) 10. [ Trashed filters ](#content-trashed-filters) 11. [ Callback filters ](#content-callback-filters) 12. [ Custom filters ](#content-custom-filters) 13. [ Filter aliases ](#content-filter-aliases) 14. [ Ignored filters values ](#content-ignored-filters-values) 15. [ Default Filter Values ](#content-default-filter-values) 16. [ Nullable Filter ](#content-nullable-filter) 17. [ Filter groups (OR / AND conjunction) ](#content-filter-groups-or--and-conjunction) 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 ----------------------------------------------------------------------------------------- ``` // 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: ``` // 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. ``` '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. These filters ignore nested arrays in their values while preserving scalar siblings. For example, `filter[name][0][x]=ignored&filter[name][1]=John` filters for `John`. If no usable values remain, no filter constraint is added, including for related properties. 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. ``` 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: ``` 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. ``` 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: ``` 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: ``` $addRelationConstraint = false; QueryBuilder::for(User::class) ->join('posts', 'posts.user_id', 'users.id') ->allowedFilters(AllowedFilter::exact('posts.title', null, $addRelationConstraint)); ``` BelongsTo filters ----------------------------------------------------------------------------------------------------------- In Model: ``` class Comment extends Model { public function post(): BelongsTo { return $this->belongsTo(Post::class); } } ``` ``` QueryBuilder::for(Comment::class) ->allowedFilters( AllowedFilter::belongsTo('post'), ) ->get(); ``` Alias ``` QueryBuilder::for(Comment::class) ->allowedFilters( AllowedFilter::belongsTo('post_id', 'post'), ) ->get(); ``` Nested ``` class Post extends Model { public function author(): BelongsTo { return $this->belongsTo(User::class); } } ``` ``` 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: ``` public function scopeStartsBefore(Builder $query, $date): Builder { return $query->where('starts_at', '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 a model argument is missing from a requested scope filter, or its value cannot be resolved to a model, an `InvalidFilterValue` exception is thrown. 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 ``` // 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: ``` 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: ``` 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: ``` 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: ``` 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: ``` use Spatie\QueryBuilder\Filters\Filter; use Illuminate\Database\Eloquent\Builder; use Illuminate\Database\Eloquent\Model; /** * @implements Filter */ 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` 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: ``` 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. ``` 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. ``` // 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. ``` 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. ``` // 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](https://gist.github.com/e0ipso/efcc4e96ca2aed58e32948e4f70c2460)extension recommendation; the JSON:API base spec's `filter` parameter family is intentionally extension-friendly. ### Basic usage ``` use Spatie\QueryBuilder\AllowedFilter; QueryBuilder::for(User::class) ->allowedFilters( AllowedFilter::groupOr('q', [ AllowedFilter::partial('name'), AllowedFilter::partial('full_name'), ]), ); ``` `/users?filter[q]=John` → ``` WHERE (LOWER(name) LIKE LOWER('%John%') OR LOWER(full_name) LIKE LOWER('%John%')) ``` ### `groupAnd` ``` AllowedFilter::groupAnd('match', [ AllowedFilter::partial('name'), AllowedFilter::partial('full_name'), ]); ``` `/users?filter[match]=John` → ``` WHERE (LOWER(name) LIKE LOWER('%John%') AND LOWER(full_name) LIKE LOWER('%John%')) ``` ### Multiple independent groups Groups are AND-joined to each other: ``` ->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` → ``` 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): ``` ->allowedFilters( AllowedFilter::partial('name'), AllowedFilter::groupOr('q', [ AllowedFilter::partial('name'), AllowedFilter::partial('full_name'), ]), ) ``` `/users?filter[name]=Ali&filter[q]=John` → ``` 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: ``` 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. A good match? ------------- ### What we do best - All things Laravel - Custom frontend components - Building APIs - AI-powered features - Simplifying things - Clean solutions - Integrating services ### Not our cup of tea - WordPress themes - Cutting corners - Free mockups to win a job - "Just execute the briefing" In short: we'd like to be a **substantial part** of your project. [ Get in touch via email ](mailto:info@spatie.be?subject=A%20good%20match%21&body=Tell%20us%20as%20much%20as%20you%20can%20about%0A-%20your%20online%20project%0A-%20your%20planning%0A-%20your%20budget%0A-%20%E2%80%A6%0A%0AAnything%20that%20helps%20us%20to%20start%20straightforward%21)