- Avoid writing custom query logic for filtering your models.
- Simply define allowed filters on your models and pass filters from http request to the model.
Features:
- Filter
- Many filter methods.
- Filter by fields.
- Filter by relationship existence.
- Filter by fields on relationships.
- No duplicated unnecessary exist clauses in queries.
- Filter by fields on relationships.
- Alias fields and relationships.
- Specify filter types per field/relationship.
- Filter json columns.
- Json path wildcard support.
- Custom filters.
- Sort
- Sort by fields.
- Alias fields.
use IndexZer0\EloquentFiltering\Contracts\IsFilterable;
use IndexZer0\EloquentFiltering\Filter\Traits\Filterable;
use IndexZer0\EloquentFiltering\Filter\Filterable\SomeFiltersAllowed;
use IndexZer0\EloquentFiltering\Filter\Filterable\Filter;
class Product extends Model implements IsFilterable
{
use Filterable;
public function allowedFilters(): SomeFiltersAllowed
{
return Filter::only(
Filter::field('name', ['$eq']),
Filter::relation('manufacturer', ['$has'])->includeRelationFields()
);
}
public function manufacturer(): HasOne
{
return $this->hasOne(Manufacturer::class);
}
}
class Manufacturer extends Model implements IsFilterable
{
use Filterable;
public function allowedFilters(): SomeFiltersAllowed
{
return Filter::only(
Filter::field('name', ['$eq'])
);
}
}
$filters = [
[
'target' => 'name',
'type' => '$eq',
'value' => 'TV',
],
[
'type' => '$has',
'target' => 'manufacturer',
'value' => [
[
'type' => '$eq',
'target' => 'name',
'value' => 'Sony',
]
]
]
];
$sql = Product::filter($filters)->toRawSql();
SELECT *
FROM "products"
WHERE "name" = 'TV'
AND EXISTS (SELECT *
FROM "manufacturers"
WHERE "products"."manufacturer_id" = "manufacturers"."id"
AND "name" = 'Sony')
- Simple Example
- Requirements
- Installation
- Usage
- Changelog
- PHP Version >= 8.2
- Laravel Version >= 10
You can install the package via composer:
composer require indexzer0/eloquent-filtering
Run the install
artisan command to publish the config and service provider:
php artisan eloquent-filtering:install
- Implement
IsFilterable
interface. - Use
Filterable
trait. - Define
allowedFilters()
method.
use IndexZer0\EloquentFiltering\Contracts\IsFilterable;
use IndexZer0\EloquentFiltering\Filter\Traits\Filterable;
use IndexZer0\EloquentFiltering\Filter\Contracts\AllowedFilterList;
use IndexZer0\EloquentFiltering\Filter\Filterable\Filter;
class Product extends Model implements IsFilterable
{
use Filterable;
public function allowedFilters(): AllowedFilterList
{
return Filter::only(
Filter::field('name', ['$eq']),
);
}
}
By default, all filters are disallowed.
You can specify allowed filters in two ways:
use IndexZer0\EloquentFiltering\Contracts\IsFilterable;
use IndexZer0\EloquentFiltering\Filter\Filterable\Filter;
use IndexZer0\EloquentFiltering\Filter\Traits\Filterable;
use IndexZer0\EloquentFiltering\Filter\Filterable\SomeFiltersAllowed;
class Product extends Model implements IsFilterable
{
use Filterable;
public function allowedFilters(): SomeFiltersAllowed
{
return Filter::only(
Filter::field('name', ['$eq', '$like']),
Filter::relation(
'manufacturer',
['$has', '$doesntHas'],
Filter::only(
Filter::field('name', ['$like'])
)
)
);
}
public function manufacturer(): BelongsTo
{
return $this->belongsTo(Manufacturer::class);
}
}
- Defining in
::filter()
method takes priority overallowedFilters()
on the model.
Product::filter(
$filters,
Filter::only(
Filter::field('name', ['$eq']),
Filter::relation(
'manufacturer',
['$has', '$doesntHas'],
Filter::only(
Filter::field('name', ['$like'])
)
)
)
)->get();
You can allow all filters using Filter::all()
.
Note
This feature is intended for use when you're not using any user supplied column names and have complete developer control over the filters being applied.
Caution
Allowing all filters and using filters from a HTTP request can put you at risk of sql injection due to PHP PDO can only bind values, not column names.
public function allowedFilters(): AllFiltersAllowed
{
return Filter::all();
}
By default, when specifying an allowed relation filter, fields within that relationship are not included in the allowed filter list.
You can specify allowed filters inside a relation in two ways.
- Define them within
Filter::relation()
as 3rd parameter.
public function allowedFilters(): SomeFiltersAllowed
{
return Filter::only(
Filter::relation(
'manufacturer', ['$has', '$doesntHas'],
Filter::only(
Filter::field('name', ['$like'])
)
)
);
}
- Use
->includeRelationFields()
onFilter::relation()
.
This method instructs the package to look for AllowedField
filters within the allowedFilters()
method of the relation model.
public function allowedFilters(): SomeFiltersAllowed
{
return Filter::only(
Filter::relation('manufacturer', ['$has', '$doesntHas'])->includeRelationFields()
);
}
Important
The relationship method MUST have return type specified, and the related model MUST also implement IsFilterable
.
- Filters ALWAYS have a
type
. - All filters apart from
$or
and$and
have atarget
. - Filter
value
is different depending on the filter.
This package provides core filters that give you the ability to perform the vast majority of the filtering you'd need.
Filter | Code | Query |
---|---|---|
EqualFilter | $eq |
{$target} = {$value} |
NotEqualFilter | $notEq |
{$target} != {$value} |
GreaterThanFilter | $gt |
{$target} > {$value} |
GreaterThanEqualToFilter | $gte |
{$target} >= {$value} |
LessThanFilter | $lt |
{$target} < {$value} |
LessThanEqualToFilter | $lte |
{$target} <= {$value} |
LikeFilter | $like |
{$target} LIKE '%{$value}%' |
LikeStartFilter | $like:start |
{$target} LIKE '{$value}%' |
LikeEndFilter | $like:end |
{$target} LIKE '%{$value}' |
NotLikeFilter | $notLike |
{$target} NOT LIKE '%{$value}%' |
NotLikeStartFilter | $notLike:start |
{$target} NOT LIKE '{$value}%' |
NotLikeEndFilter | $notLike:end |
{$target} NOT LIKE '%{$value}' |
NullFilter | $null |
{$target} is null || {$target} is not null |
InFilter | $in |
{$target} in ($value) |
NotInFilter | $notIn |
{$target} not in ($value) |
BetweenFilter | $between |
{$target} between $value[0] and $value[1] |
NotBetweenFilter | $notBetween |
{$target} not between $value[0] and $value[1] |
BetweenColumnsFilter | $betweenColumns |
{$target} between $value[0] and $value[1] |
NotBetweenColumnsFilter | $notBetweenColumns |
{$target} not between $value[0] and $value[1] |
JsonContainsFilter | $jsonContains |
json_contains({$target}, {$value}) |
JsonNotContainsFilter | $jsonNotContains |
not json_contains({$target}, {$value}) |
JsonLengthFilter | $jsonLength |
json_length({$target}}) $operator $value |
Filter | Code | Query |
---|---|---|
HasFilter | $has |
where exists (select * from {$target}) |
DoesntHasFilter | $doesntHas |
where not exists (select * from {$target}) |
Filter | Code | Query |
---|---|---|
OrFilter | $or |
or |
AndFilter | $and |
and |
- Accepting pull requests for more common filters.
value
=string
|int
|float
.
$sql = Person::filter([
[
'type' => '$eq',
'target' => 'name',
'value' => 'Taylor',
]
])->toRawSql();
select * from "people" where "name" = 'Taylor'
value
=string
|int
|float
.
$sql = Person::filter([
[
'type' => '$notEq',
'target' => 'name',
'value' => 'Taylor',
]
])->toRawSql();
select * from "people" where "name" != 'Taylor'
value
=string
|int
|float
.
$sql = Person::filter([
[
'type' => '$gt',
'target' => 'age',
'value' => 18,
]
])->toRawSql();
select * from "people" where "age" > 18
value
=string
|int
|float
.
$sql = Person::filter([
[
'type' => '$gte',
'target' => 'age',
'value' => 18,
]
])->toRawSql();
select * from "people" where "age" >= 18
value
=string
|int
|float
.
$sql = Person::filter([
[
'type' => '$lt',
'target' => 'age',
'value' => 18,
]
])->toRawSql();
select * from "people" where "age" < 18
value
=string
|int
|float
.
$sql = Person::filter([
[
'type' => '$lte',
'target' => 'age',
'value' => 18,
]
])->toRawSql();
select * from "people" where "age" <= 18
value
=string
|int
|float
.
$sql = Project::filter([
[
'type' => '$like',
'target' => 'description',
'value' => 'Laravel',
]
])->toRawSql();
select * from "projects" where "description" LIKE '%Laravel%'
value
=string
|int
|float
.
$sql = Project::filter([
[
'type' => '$like:start',
'target' => 'description',
'value' => 'Laravel',
]
])->toRawSql();
select * from "projects" where "description" LIKE 'Laravel%'
value
=string
|int
|float
.
$sql = Project::filter([
[
'type' => '$like:end',
'target' => 'description',
'value' => 'Laravel',
]
])->toRawSql();
select * from "projects" where "description" LIKE '%Laravel'
value
=string
|int
|float
.
$sql = Project::filter([
[
'type' => '$notLike',
'target' => 'description',
'value' => 'Laravel',
]
])->toRawSql();
select * from "projects" where "description" NOT LIKE '%Laravel%'
value
=string
|int
|float
.
$sql = Project::filter([
[
'type' => '$notLike:start',
'target' => 'description',
'value' => 'Laravel',
]
])->toRawSql();
select * from "projects" where "description" NOT LIKE 'Laravel%'
value
=string
|int
|float
.
$sql = Project::filter([
[
'type' => '$notLike:end',
'target' => 'description',
'value' => 'Laravel',
]
])->toRawSql();
select * from "projects" where "description" NOT LIKE '%Laravel'
value
=boolean
.true
foris null
.false
foris not null
.
$sql = Person::filter([
[
'type' => '$null',
'target' => 'age',
'value' => true,
],
[
'type' => '$null',
'target' => 'weight',
'value' => false,
],
])->toRawSql();
select * from "people" where "age" is null and "weight" is not null
value
= array ofstring
|int
|float
.
$sql = Person::filter([
[
'type' => '$in',
'target' => 'name',
'value' => ['Taylor', 'Otwell',]
],
])->toRawSql();
select * from "people" where "name" in ('Taylor', 'Otwell')
value
= array ofstring
|int
|float
.
$sql = Person::filter([
[
'type' => '$notIn',
'target' => 'name',
'value' => ['Nuno', 'Maduro',]
],
])->toRawSql();
select * from "people" where "name" not in ('Nuno', 'Maduro')
value
= array ofstring
|int
|float
.
$sql = Person::filter([
[
'type' => '$between',
'target' => 'age',
'value' => [18, 65,],
],
])->toRawSql();
select * from "people" where "age" between 18 and 65
value
= array ofstring
|int
|float
.
$sql = Person::filter([
[
'type' => '$notBetween',
'target' => 'age',
'value' => [18, 65,],
],
])->toRawSql();
select * from "people" where "age" not between 18 and 65
value
=array
of strings.
$sql = Product::filter([
[
'type' => '$betweenColumns',
'target' => 'price',
'value' => [
'min_allowed_price',
'max_allowed_price',
],
],
])->toRawSql();
select * from "products" where "price" between "min_allowed_price" and "max_allowed_price"
value
=array
of strings.
$sql = Product::filter([
[
'type' => '$notBetweenColumns',
'target' => 'price',
'value' => [
'min_allowed_price',
'max_allowed_price',
],
],
])->toRawSql();
select * from "products" where "price" between "min_allowed_price" and "max_allowed_price"
value
=string
|int
|float
.
$sql = User::filter([
[
'type' => '$jsonContains',
'target' => 'options->languages',
'value' => 'en',
],
])->toRawSql();
select * from "users" where json_contains(`options`, '\"en\"', '$."languages"')
value
=string
|int
|float
.
$sql = User::filter([
[
'type' => '$jsonNotContains',
'target' => 'options->languages',
'value' => 'en',
],
])->toRawSql();
select * from "users" where not json_contains(`options`, '\"en\"', '$."languages"')
operator
==
|<
|<=
|>
|>=
.value
=int
.
$sql = User::filter([
[
'type' => '$jsonLength',
'target' => 'options->languages',
'operator' => '>=',
'value' => 2,
],
])->toRawSql();
select * from "users" where json_length(`options`, '$."languages"') >= 2
value
=array
of filters.
$sql = Project::filter([
[
'type' => '$has',
'target' => 'comments',
'value' => [
[
'type' => '$like',
'target' => 'content',
'value' => 'awesome',
]
]
],
])->toRawSql();
select * from "projects" where exists (select * from "comments" where "projects"."id" = "comments"."project_id" and "content" LIKE '%awesome%')
value
=array
of filters.
$sql = Project::filter([
[
'type' => '$doesntHas',
'target' => 'comments',
'value' => [
[
'type' => '$like',
'target' => 'content',
'value' => 'boring',
]
]
],
])->toRawSql();
select * from "projects" where not exists (select * from "comments" where "projects"."id" = "comments"."project_id" and "content" LIKE '%boring%')
value
=array
of filters.
$sql = Comment::filter([
[
'type' => '$or',
'value' => [
[
'type' => '$like',
'target' => 'content',
'value' => 'awesome',
],
[
'type' => '$like',
'target' => 'content',
'value' => 'boring',
]
]
]
])->toRawSql();
select * from "comments" where (("content" LIKE '%awesome%') or ("content" LIKE '%boring%'))
value
=array
of filters.
$sql = Comment::filter([
[
'type' => '$and',
'value' => [
[
'type' => '$like',
'target' => 'content',
'value' => 'is awesome',
],
[
'type' => '$like',
'target' => 'content',
'value' => 'is not boring',
]
]
]
])->toRawSql();
select * from "comments" where (("content" LIKE '%is awesome%') and ("content" LIKE '%is not boring%'))
- You can create two different types of custom filters.
- Custom Field Filter
- Fully Custom Filter
Important
You must register your custom filter classes in the config file eloquent-filtering.php
'custom_filters' => [
YourCustomFilter::class,
],
- Usage:
Filter::field('name', ['$lowercase'])
.
php artisan make:eloquent-filter LowerCaseFilter --type=field
class LowerCaseFilter extends AbstractFieldFilter
{
final public function __construct(
protected string $target,
protected string $value,
) {
}
/*
* The unique identifier of the filter.
*/
public static function type(): string
{
return '$lowercase';
}
/*
* The format that the filter data must adhere to.
* Defined as laravel validator rules.
* On fail: throws MalformedFilterFormatException.
*/
public static function format(): array
{
return [
'target' => ['required', 'string'],
'value' => ['required', 'string'],
];
}
/*
* Instantiate filter class from ApprovedFilter.
*/
public static function from(ApprovedFilter $approvedFilter): static
{
return new static(
$approvedFilter->target()->getReal(),
$approvedFilter->data_get('value'),
);
}
/*
* Apply the filter logic.
*/
public function apply(Builder $query): Builder
{
return $query->where(
DB::raw("LOWER({$this->target})"),
strtolower($this->value)
);
}
}
/*
* Usage:
*/
public function allowedFilters(): SomeFiltersAllowed
{
return Filter::only(
Filter::field('name', ['$lowercase']),
);
}
- Generally for use when there is no user specified target field.
php artisan make:eloquent-filter AdminFilter --type=custom
class AdminFilter extends AbstractCustomFilter
{
/*
* The unique identifier of the filter.
*/
public static function type(): string
{
return '$admin';
}
/*
* Apply the filter logic.
*/
public function apply(Builder $query): Builder
{
return $query->where('admin', true);
}
}
/*
* Usage:
*/
public function allowedFilters(): SomeFiltersAllowed
{
return Filter::only(
Filter::custom(['$admin']),
);
}
- Default configuration file
return [
'default_allowed_filter_list' => 'none',
'default_allowed_sort_list' => 'none',
'suppress' => [
'filter' => [
'invalid' => false,
'missing' => false,
'malformed_format' => false,
'denied' => false,
],
'sort' => [
'malformed_format' => false,
'denied' => false,
],
],
'custom_filters' => [
],
];
- The package throws various exception which can be suppressed.
- Custom filters should be registered in the config.
Note
This feature is intended for use when you're not using any user supplied column names and have complete developer control over the filters being applied.
You can change the default allowed filters within the config file eloquent-filtering.php
.
'default_allowed_filter_list' => 'all',
Caution
Allowing all filters by default and using filters from a HTTP request can put you at risk of sql injection due to PHP PDO can only bind values, not column names.
It is strongly suggested that you keep default_allowed_filter_list
as none
in your config and explicitly allow only specific filters with Filter::only()
.
You can alias your target fields and relations if you don't wish to expose database field names and relationship method names to your frontend.
The below example:
- Allows
name
and usesfirst_name
in the database query. - Allows
documents
and usesfiles
as the relationship name.
$sql = Person::filter([
[
'type' => '$eq',
'target' => 'name',
'value' => 'Taylor',
],
[
'type' => '$has',
'target' => 'documents',
'value' => [],
],
], Filter::only(
Filter::field(Target::alias('name', 'first_name'), ['$eq']),
Filter::relation(Target::alias('documents', 'files'), ['$has'])
))->toRawSql();
You can also alias targets when allowing all filters.
Filter::all(
Target::alias('name', 'first_name'),
Target::relationAlias(
'documents',
'files',
Target::alias('file_extension', 'mime_type')
),
)
- When specifying the target of a json database field you can specify wildcards in the json path.
Filter::only(
Filter::field('data->*->array', ['$jsonContains']),
)
/*
* Allows:
*/
$filters = [
[
'type' => '$jsonContains',
'target' => 'data->languages->array',
'value' => [
'en',
'de',
]
]
];
use IndexZer0\EloquentFiltering\Filter\Types\Types;
// Only `$eq` allowed
Filter::field('name', ['$eq'])
Filter::field('name', Types::only(['$eq']))
// All types allowed
Filter::field('name', Types::all()),
// All except `$eq` allowed
Filter::field('name', Types::except(['$eq'])),
Various exceptions are thrown by this package. Most can be suppressed globally in the config file.
When suppressing an exception, filters that caused the exception will be ignored.
- Suppressible
class InvalidFilterException
config("eloquent-filtering.suppress.filter.invalid");
// Filter does not have `type` key.
class MissingFilterException
config("eloquent-filtering.suppress.filter.missing");
// Can't find filter of `type` specified.
class MalformedFilterFormatException
config("eloquent-filtering.suppress.filter.malformed_format");
// The filter was found, but the rest of the data does not match required format of the filter.
class DeniedFilterException
config("eloquent-filtering.suppress.filter.denied");
// Filter is not allowed.
- Not Suppressible
class DuplicateFiltersException
// When you have registered a custom filter that has the same type as another filter.
You can hook into the suppression system if you want to perform some custom actions.
use IndexZer0\EloquentFiltering\Suppression\Suppression;
Suppression::handleDeniedFilterUsing(function (SuppressibleException $se): void {
Log::channel('slack')->info('Bug in frontend client, trying to use filter type that is not allowed: ' . $se->getMessage());
throw new FrontendBugException($se->getMessage());
});
Available suppression hooks.
// All
Suppression::handleAllUsing();
// Filter
Suppression::handleFilterUsing();
Suppression::handleInvalidFilterUsing();
Suppression::handleMissingFilterUsing();
Suppression::handleMalformedFilterUsing();
Suppression::handleDeniedFilterUsing();
// Sort
Suppression::handleSortUsing();
Suppression::handleMalformedSortUsing();
Suppression::handleDeniedSortUsing();
The condition filters $or
, and $and
are not required to be specified when allowing filters.
These filters are always allowed, due to these filters essentially being wrappers around other filters.
All exceptions thrown by the package implement \IndexZer0\EloquentFiltering\Contracts\EloquentFilteringException
.
How-ever it doesn't harm to also catch \Throwable
.
try {
Person::filter([])->get();
} catch (\IndexZer0\EloquentFiltering\Contracts\EloquentFilteringException $exception) {
$exception->getMessage();
} catch (\Throwable $t) {
// Shouldn't happen - but failsafe.
}
composer test
Please see CHANGELOG for more information on what has changed recently.
Please see Feature ideas for potential future features.
The MIT License (MIT). Please see License File for more information.