Download the PHP package gency/laravel-filterable without Composer

Gency\Filterable

A trait to provide composable queries to Laravel models.

The intended usage is to provide an easy way to declare how a model's fields are filterable and allow the filter query to be expressed in a form that can be easily represented (as a JSON object for example).

The Gency\Filterable\FilterableTrait adds a filter() method to a Model which works with the Laravel query builder. The filter() method accepts a single argument which is an array of [field => value] pairs that define the search query being made.

Install

Usage

Basic usage is to add the trait to a Model class and configure filterable fields.

The $filterable property defines the fields that may be used in filter queries. The value is an array of filter rules that lists the possible variations the filter may use.

The built-in rules are:

• EQ - equality
• LIKE - SQL like
• ILIKE - a case-insensitive version of LIKE
• MATCH - wildcard pattern matching
• MIN - greater than or equal to
• MAX - less than or equal to
• GT - greater than
• LT - less than
• RE - regular expression
• FT - full text search (see notes below)
• IN - contained in list
• NULL - null comparison

A standard set of rules are provided

• String = [EQ, LIKE, ILIKE, MATCH]
• Numeric = [EQ, MIN, MAX, LT, GT]
• Enum = [EQ, IN]
• Date = [EQ, MIN, MAX, LT, GT]
• Boolean = [EQ]

A model's filterable definition sets the rules available for each field. The first rule in the list is the default rule for the field. Other rules must add the rule name as a suffix in the filter query field name.

In the following definition, the 'name' field can use any of the String rules: EQ, LIKE, ILIKE, MATCH.

A query can thus use:

The SQL that is run will match any user whose name is a case-insensitive match containing 'John', such as 'Little john', 'Johnathon', 'JOHN'.

The model's filterable definition can also be return dynamically by overloading the getFilterable() method on the class.

Custom rules

A class may provide custom rules to apply to fields.

Custom rules can be listed in the $filterable definition along with the built-in rules and work in the same way. The first rule in $filterable is the default rule for the field. If it's not the first rule it must have the rule name added as a suffix to the field name in the query.

Rule names are converted to 'ucfirst' and appended to 'scopeFilter'.

Negating rules

Rules may be negated by using the 'NOT' modifier for default rules, or prefixing the rule modifier with 'NOT_' for other rules.

Note: the comparison rules (MIN, MAX, LT, GT) do not have negated forms.

Custom rules may implemented a negated version by defining a corresponding scope function that implements the functionality. The function is named similarly, but with the word 'Not' before the rule name.

Logical combinations with AND, OR, NOT, and NOR

A filter can create more complex queries by structuring the filter into nested queries.

The 'AND', 'OR', 'NOT', and 'NOR' nesting operators each take a list of nested filters to apply. Nested filter queries can in turn use the nesting operators to create more complex queries.

Relationships

Relationships can be used to filter related models.

Chaining filters

Filters are deeply merged when chaining.

To not do a deep merge the filterApply() method can be used.

Full text search

The Filterable::FT rule provides a basic form of full text search in PostgreSQL using tsearch.

To make use of the full text rule the application must provide a table populated with search data. By default, the table is named according to the model name with _filterable as suffix, and has a one-to-one mapping using the same primary key as the model's table. The tsearch vector data is stored in a column using the field's name with _vector suffix. The table and vector field names can be customised by provide values for the filterableFtTable and filterableFtVector properties.

The application must ensure the vector field is appropriately updated (usually by defining trigger functions).

The model may provide custom values for the search table, foreign key, and field names.

These custom values may also be evaluated dynamically by providing "get" functions.