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/v3)  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)

- [ Introduction ](https://spatie.be/docs/laravel-query-builder/v3/introduction)
- [ Requirements ](https://spatie.be/docs/laravel-query-builder/v3/requirements)
- [ About us ](https://spatie.be/docs/laravel-query-builder/v3/about-us)
- [ Installation & setup ](https://spatie.be/docs/laravel-query-builder/v3/installation-setup)
- [ Support us ](https://spatie.be/docs/laravel-query-builder/v3/support-us)
- [ Questions and issues ](https://spatie.be/docs/laravel-query-builder/v3/questions-issues)
- [ Changelog ](https://spatie.be/docs/laravel-query-builder/v3/changelog)

Features
--------

- [ Filtering ](https://spatie.be/docs/laravel-query-builder/v3/features/filtering)
- [ Sorting ](https://spatie.be/docs/laravel-query-builder/v3/features/sorting)
- [ Including relationships ](https://spatie.be/docs/laravel-query-builder/v3/features/including-relationships)
- [ Selecting fields ](https://spatie.be/docs/laravel-query-builder/v3/features/selecting-fields)
- [ Appending attributes ](https://spatie.be/docs/laravel-query-builder/v3/features/appending-attributes)

Advanced usage
--------------

- [ Extending query builder ](https://spatie.be/docs/laravel-query-builder/v3/advanced-usage/extending-query-builder)
- [ Pagination ](https://spatie.be/docs/laravel-query-builder/v3/advanced-usage/pagination)
- [ Front-end implementation ](https://spatie.be/docs/laravel-query-builder/v3/advanced-usage/front-end-implementation)
- [ Multi value delimiter ](https://spatie.be/docs/laravel-query-builder/v3/advanced-usage/multi-value-delimiter)

      You are viewing the documentation for **an older version** of this package. You can check the version you are using with the following command:

 `                                    composer show spatie/laravel-query-builder                                                                                                                                                                                                                                    `

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. [ Exact filters ](#content-exact-filters)
5. [ Exact or partial filters for related properties ](#content-exact-or-partial-filters-for-related-properties)
6. [ Scope filters ](#content-scope-filters)
7. [ Trashed filters ](#content-trashed-filters)
8. [ Callback filters ](#content-callback-filters)
9. [ Custom filters ](#content-custom-filters)
10. [ Filter aliases ](#content-filter-aliases)
11. [ Ignored filters values ](#content-ignored-filters-values)
12. [ Default Filter Values ](#content-default-filter-values)

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 an array of strings or `AllowedFilter` instances. An allowed filter can be partial, 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
```

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.

```
'disable_invalid_filter_query_exception' => true
```

By default the option is set false.

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

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));
```

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

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 `AllowedFilters::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 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;

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

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),
    ])
    ->get();
```