Filter Records
This functionality allows you to filter eloquent model records by defining filter rules, similar to how Laravel's form request rules work.
Usage
Your models should use the Varbox\Traits\IsFilterable
trait.
<?php
namespace App;
use Illuminate\Database\Eloquent\Model;
use Varbox\Traits\IsFilterable;
class YourModel extends Model
{
use IsFilterable;
...
}
Next, you should create a filter class to define the filtering rules. It's recommended you keep all your filter classes inside an app/Filters
directory.
To learn more about what a filter class should contain please read this section
Your filter class should extend the Varbox\Filters\Filter
and implement the filters()
, morph()
and modifiers()
methods.
<?php
namespace App\Filters;
use Varbox\Filters\Filter;
class YourFilter extends Filter
{
/**
* Get the filters that apply to the request.
*
* @return array
*/
public function filters()
{
return [
'keyword' => [
'operator' => Filter::OPERATOR_LIKE,
'condition' => Filter::CONDITION_OR,
'columns' => 'name,content',
],
'status' => [
'operator' => Filter::OPERATOR_EQUAL,
'condition' => Filter::CONDITION_OR,
'columns' => 'status',
],
'date' => [
'operator' => Filter::OPERATOR_DATE_GREATER_OR_EQUAL,
'condition' => Filter::CONDITION_OR,
'columns' => 'date',
],
];
}
/**
* Get the main where condition between entire request fields.
*
* @return string
*/
public function morph()
{
return 'and';
}
/**
* Get the modified value of a request filter field.
*
* @return array
*/
public function modifiers()
{
return [];
}
}
Once you've completed the above steps you can filter your model records by using the filtered
query scope.
This query scope accepts two parameters:
- an array representing the key / value pairs on which filtering will work
- an instance of your filter class that will define the filtering logic
YourModel::filtered($request->all(), new YourFilter)->get();
Filter By Relation
You can also filter model records by a field from a relation.
To specify that you want to filter a certain field by a related field, you should specify {relationName.columnName}
as the value for the columns
key present on the filters()
method.
public function filters()
{
return [
'keyword' => [
'operator' => Filter::OPERATOR_LIKE,
'condition' => Filter::CONDITION_OR,
'columns' => 'relation.column',
],
];
}
Filter Class Explained
Up until this point you've learned how to implement the filtering functionality on your models, but you might still have some questions about how the filter class actually works and what it should contain.
The filter class is used to define the filtering logic on which the filtered
query scope will function.
The filters
Method
This method is used to define your actual filtering rules, that will later on be passed to the filtered
query scope.
This method should always return an array of this format:
public function filters()
{
return [
'{query_field}' => [
'operator' => Filter::OPERATOR_LIKE,
'condition' => Filter::CONDITION_OR,
'columns' => 'name,content',
],
// append as many rules you want for as many query fields
];
}
The {query_field}
should be replaced with the name of your actual query field from your url.
For this url: /search?keyword=test&status=1
you should replace {query_field}
with either keyword
or status
.
The operator
defines the way filtering should be done for the respective query field.
You can choose from a variety of filter operators listed here.
The condition
applies only if you specify multiple columns for a query field.
It defines how filtering between the columns should behave.
The available values are: Filter::CONDITION_AND
or FILTER::CONDITION_OR
The columns
defines on which database table columns the query should filter against.
You can specify multiple columns by using comma (,)
between column names.
The morph
Method
This method defines how filtering conditions between multiple query fields should be handled.
It should return either and
or or
.
public function morph()
{
return 'and';
}
If your morph
method returns and
the final query will look like this:
->where('query_field_1', 'some value')
->where('query_field_2', 'some value')
->...
If your morph
method returns or
the final query will look like this:
->orWhere('query_field_1', 'some value')
->orWhere('query_field_2', 'some value')
->...
The modifiers
method
In most cases this method will return an empty array
.
However, you can use this method if you want to filter by a value, but your database table column holds that value in another format.
A good example for this would be to filter files by size in megabytes
, but you store the size in bytes
at database level.
public function modifiers()
{
return [
// assuming you have "max_size" defined your "filters()" method
'max_size' => function ($modified) {
return request()->query('size')* pow(1024, 2);
},
];
}
Available Filter Operators
The Varbox\Filters\Filter
class exposes a few filter operators that you can use inside your filters()
method in order to define the filter behavior for certain query string fields.
Filter::OPERATOR_EQUAL Filter::OPERATOR_NOT_EQUAL Filter::OPERATOR_SMALLER Filter::OPERATOR_GREATER Filter::OPERATOR_SMALLER_OR_EQUAL Filter::OPERATOR_GREATER_OR_EQUAL Filter::OPERATOR_NULL Filter::OPERATOR_NOT_NULL Filter::OPERATOR_IN Filter::OPERATOR_NOT_IN Filter::OPERATOR_LIKE Filter::OPERATOR_NOT_LIKE Filter::OPERATOR_BETWEEN Filter::OPERATOR_NOT_BETWEEN Filter::OPERATOR_DATE Filter::OPERATOR_DATE_EQUAL Filter::OPERATOR_DATE_NOT_EQUAL Filter::OPERATOR_DATE_SMALLER Filter::OPERATOR_DATE_GREATER Filter::OPERATOR_DATE_SMALLER_OR_EQUAL Filter::OPERATOR_DATE_GREATER_OR_EQUAL
Implementation Example
For an implementation example of this functionality please refer to the Full Example page.