README

WIP - Public api subject to change.

Avoid writing custom query logic for filtering your models.

Features:

Filter
- Many filter methods.
- Filter by fields.
- Filter by relationship existence.
  - Filter by fields on relationships.
    - No duplicated unnecessary exist clauses in queries.
- Alias fields and relationships.
- Specify filter types per field/relationship.
- Json column filters.
- Custom filters.
Sort
- Sort by fields.
- Alias fields.
Terminology based on laravel eloquent query builder.

Simple example with relationship filter.

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

Requirements

PHP Version >= 8.2
Laravel Version >= 10

Installation

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

Usage

Making Model Filterable

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']),
        );
    }
}

Allowing Filters

By default, all filters are disallowed.

You can specify allowed filters in two ways:

Define on model.

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

Define in `::filter()`

Defining in ::filter() method takes priority over allowedFilters() on the model.

Product::filter(
    $filters,
    Filter::only(
        Filter::field('name', ['$eq']),
        Filter::relation(
            'manufacturer', 
            ['$has', '$doesntHas'],
            Filter::only(
                Filter::field('name', ['$like'])
            )
        )
    )
)->get();

Allowing All Filters

You can allow all filters using Filter::all().

See Caution in Default Allowed Filters section for security concerns when using this feature.

public function allowedFilters(): AllFiltersAllowed
{
    return Filter::all();
}

Including Relationship Model Filters

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() on Filter::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.

Filter Structure

Filters ALWAYS have a type.
All filters apart from $or and $and have a target.
Filter value is different depending on the filter.

Available Filters

This package provides core filters that give you the ability to perform the vast majority of the filtering you'd need.

Field Filters

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

Relationship Filters

Filter	Code	Query
HasFilter	`$has`	`where exists (select * from {$target})`
DoesntHasFilter	`$doesntHas`	`where not exists (select * from {$target})`

Condition Filters

Filter	Code	Query
OrFilter	`$or`	`or`
AndFilter	`$and`	`and`

See Conditional Filters Note

Json Field Filters

Filter	Code	Query
JsonContainsFilter	`$jsonContains`	`{$target} not between $value[0] and $value[1]`

Accepting pull requests for more common filters.

Filter Examples

EqualFilter - `$eq`

value = string | int | float.

$sql = Person::filter([
    [
        'type'   => '$eq',
        'target' => 'name',
        'value'  => 'Taylor',
    ]
])->toRawSql();

select * from "people" where "name" = 'Taylor'

NotEqualFilter - `$notEq`

value = string | int | float.

$sql = Person::filter([
    [
        'type'   => '$notEq',
        'target' => 'name',
        'value'  => 'Taylor',
    ]
])->toRawSql();

select * from "people" where "name" != 'Taylor'

GreaterThanFilter - `$gt`

value = string | int | float.

$sql = Person::filter([
    [
        'type'   => '$gt',
        'target' => 'age',
        'value'  => 18,
    ]
])->toRawSql();

select * from "people" where "age" > 18

GreaterThanEqualToFilter - `$gte`

value = string | int | float.

$sql = Person::filter([
    [
        'type'   => '$gte',
        'target' => 'age',
        'value'  => 18,
    ]
])->toRawSql();

select * from "people" where "age" >= 18

LessThanFilter - `$lt`

value = string | int | float.

$sql = Person::filter([
    [
        'type'   => '$lt',
        'target' => 'age',
        'value'  => 18,
    ]
])->toRawSql();

select * from "people" where "age" < 18

LessThanEqualToFilter - `$lte`

value = string | int | float.

$sql = Person::filter([
    [
        'type'   => '$lte',
        'target' => 'age',
        'value'  => 18,
    ]
])->toRawSql();

select * from "people" where "age" <= 18

LikeFilter - `$like`

value = string | int | float.

$sql = Project::filter([
    [
        'type'   => '$like',
        'target' => 'description',
        'value'  => 'Laravel',
    ]
])->toRawSql();

select * from "projects" where "description" LIKE '%Laravel%'

LikeStartFilter - `$like:start`

value = string | int | float.

$sql = Project::filter([
    [
        'type'   => '$like:start',
        'target' => 'description',
        'value'  => 'Laravel',
    ]
])->toRawSql();

select * from "projects" where "description" LIKE 'Laravel%'

LikeEndFilter - `$like:end`

value = string | int | float.

$sql = Project::filter([
    [
        'type'   => '$like:end',
        'target' => 'description',
        'value'  => 'Laravel',
    ]
])->toRawSql();

select * from "projects" where "description" LIKE '%Laravel'

NotLikeFilter - `$notLike`

value = string | int | float.

$sql = Project::filter([
    [
        'type'   => '$notLike',
        'target' => 'description',
        'value'  => 'Laravel',
    ]
])->toRawSql();

select * from "projects" where "description" NOT LIKE '%Laravel%'

NotLikeStartFilter - `$notLike:start`

value = string | int | float.

$sql = Project::filter([
    [
        'type'   => '$notLike:start',
        'target' => 'description',
        'value'  => 'Laravel',
    ]
])->toRawSql();

select * from "projects" where "description" NOT LIKE 'Laravel%'

NotLikeEndFilter - `$notLike:end`

value = string | int | float.

$sql = Project::filter([
    [
        'type'   => '$notLike:end',
        'target' => 'description',
        'value'  => 'Laravel',
    ]
])->toRawSql();

select * from "projects" where "description" NOT LIKE '%Laravel'

OrFilter - `$or`

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%'))

AndFilter - `$and`

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%'))

NullFilter - `$null`

value = boolean for is null or is 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

InFilter - `$in`

value = array of values.

$sql = Person::filter([
    [
        'type'   => '$in',
        'target' => 'name',
        'value'  => ['Taylor', 'Otwell',]
    ],
])->toRawSql();

select * from "people" where "name" in ('Taylor', 'Otwell')

NotInFilter - `$notIn`

value = array of filters.

$sql = Person::filter([
    [
        'type'   => '$notIn',
        'target' => 'name',
        'value'  => ['Nuno', 'Maduro',]
    ],
])->toRawSql();

select * from "people" where "name" not in ('Nuno', 'Maduro')

BetweenFilter - `$between`

value = string | int | float.

$sql = Person::filter([
    [
        'type'   => '$between',
        'target' => 'age',
        'value'  => [18, 65,],
    ],
])->toRawSql();

select * from "people" where "age" between 18 and 65

NotBetweenFilter - `$notBetween`

value = string | int | float.

$sql = Person::filter([
    [
        'type'   => '$notBetween',
        'target' => 'age',
        'value'  => [18, 65,],
    ],
])->toRawSql();

select * from "people" where "age" not between 18 and 65

BetweenColumnsFilter - `$betweenColumns`

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"

NotBetweenColumnsFilter - `$notBetweenColumns`

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"

HasFilter - `$has`

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%')

DoesntHasFilter - `$doesntHas`

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%')

Digging Deeper

Config

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.

Default Allowed Filters

You can change the default allowed filters within the config file eloquent-filtering.

'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().

This feature is intended for use when you're not using any user supplied information and have complete developer control over the filters being applied.

Aliasing Targets

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 uses first_name in the database query.
Allows documents and uses files 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')
    ),
)

Specifying Allowed Types

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'])),

Suppressing Exceptions

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.

Suppression Hooks

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

Custom Filters

TODO

Condition Filters Note

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.

Error Handling

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.
}

Testing

composer test

indexzer0 / eloquent-filtering

Maintainers

Details

README

WIP - Public api subject to change.

Simple example with relationship filter.

Requirements

Installation

Usage

Making Model Filterable

Allowing Filters

Define on model.

Define in ::filter()

Allowing All Filters

Including Relationship Model Filters

Filter Structure

Available Filters

Field Filters

Relationship Filters

Condition Filters

Json Field Filters

Filter Examples

EqualFilter - $eq

NotEqualFilter - $notEq

GreaterThanFilter - $gt

GreaterThanEqualToFilter - $gte

LessThanFilter - $lt

LessThanEqualToFilter - $lte

LikeFilter - $like

LikeStartFilter - $like:start

LikeEndFilter - $like:end

NotLikeFilter - $notLike

NotLikeStartFilter - $notLike:start

NotLikeEndFilter - $notLike:end

OrFilter - $or

AndFilter - $and

NullFilter - $null

InFilter - $in

NotInFilter - $notIn

BetweenFilter - $between

NotBetweenFilter - $notBetween

BetweenColumnsFilter - $betweenColumns

NotBetweenColumnsFilter - $notBetweenColumns

HasFilter - $has

DoesntHasFilter - $doesntHas