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:

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.