Download the PHP package hyvor/laravel-filterq without Composer

Introduction

FilterQ allows advanced filtering in Laravel APIs. For example, you can get a single-line input like this from your users.

Then, FilterQ can convert it to WHERE statements in Laravel Query Builder like this:

or in SQL:

It's like making it possible for your API consumers to filter data directly using SQL, but securely.

FilterQ was built for Hyvor Blogs' Data API.

Features

• Easy-to-write, single or multi-line expressions.
• Logical operators (& and |) and nesting/grouping (with ())
• Secure. FilterQ only gives access to the columns and operators you define.
• Supports joining tables. Users can not only filter by columns but also via joined tables (Amazing, right?)
• Supports "type hinting" for keys.
• Extensible. You can add your own operators easily (ex: SQL LIKE).

FilterQ Expressions

Example: (published_at > 1639665890 & published_at < 1639695890) | is_featured=true

A FitlerQ Expression is a combination of conditions, connected and grouped using one or more of the following.

• & - AND
• | - OR
• () - to group logic

A condition has three parts:

• key
• operator
• value

Key

Usually, a key is a column name of the table. But, it can also be something else where you use a custom handler to create the where part in Laravel.

It should match [a-zA-Z0-9_.]+. For example, key, key_2, key.child are valid.

Operators

By default, the following operators are supported.

• = - equals
• != - not equal
• > - greater than
• < - less than
• >= - greater than or equals
• <= - less than or equals

If you want to add more operators (ex: an operator for SQL LIKE), see Custom Operators.

Values

• null: null
• boolean: true or false
• strings: 'hey' or hey
  • Strings without quotes should match [a-zA-Z_][a-zA-Z0-9_-]+.
  • should start with a letter
  • can contain alphanumeric characters, dashes, or underscores
  • no spaces are allowed
  • it cannot be true, false, or null
• number: 250, -250, or 2.5

Basic Usage

FilterQ is a Laravel Facade, so you can start with any method you like. The last method must be addWhere().

Let's learn step by step.

1. Setting the FilterQ Expression and Builder

In most cases, you will get a single input in your API endpoints as the FilterQ expression. Therefore, here's an example with a controller.

• FilterQ::expression() sets the FilterQ expression
• builder() sets the Laravel Query. It accepts a Laravel Query Builder, Eloquent Query Builder, Relation, or a Model.

2. Set Keys

Setting keys is important. Laravel uses prepared statements to prevent SQL injection. But, prepared statements only secure "data", not column names. Therefore, to prevent SQL injection, you have to define all keys (or columns) you allow the user to define within the FitlerQ expression.

Here, we define two keys: id and slug. So, users can use these two keys in their FilterQ expressions. Using any other will throw an error.

• keys() takes a closure that will be called with a $keys (Hyvor\FilterQ\Keys) object.
  • You must call the $keys->add($key) to registers a key. It returns a Hyvor\FilterQ\Key object, which you can chain to add more options.
  • Key::column() sets the column name. If this is not called, FilterQ uses the key name as the column name.
  • Key::join() sets a join.
  • Key::operators() sets supported operators
  • Key::valueType() defines supported value types
  • Key::values() defines supported values

3. Finally, call addWhere()

After all the above operations, call the addWhere() method. This will add where statements to the builder you provided and will return the query builder itself. You can then do other operations like limit, orderBy, and even add more where statements. Finally, call get() to get results.

Joins

Sometimes, you want to join a foreign table when a key is present. Let's see this example.

• You have two tables: posts and authors.
• You have a /posts endpoint with FilterQ expressions support.
• You now want to allow users to filter posts by author.name

In this example, two things are done on the Post::class query builder.

1. A ->join() is added to the query builder because the author.name key is present.
2. A ->where() is added for authors.name column.

It is important to note that even if the same key is present multiple times, only one join will only be added.

Here's what the above query will look like in SQL:

->join() function takes the same arguments as the Laravel's Join.

Left and Right Joins

The above example makes an INNER JOIN. If you want to add left or right joins, use the fourth parameter.

JOIN with a callback

If you want to add Advanced Joins or Subquery Joins, use a callback.

Key Operators

It is possible (and recommended) to define what operators are allowed by a key.

Key Value Types

It is possible (and highly recommended) to define what value types are supported by a key.

The valueType method supports the following types:

Scalar:

• int
• float
• string
• null
• bool

Special:

• numeric - int, float, or numeric string (uses PHP's is_numeric)
• date - A valid date/time string or an integer UNIX timestamp. (PHP's strtotime function is used to parse, therefore relative dates like "-7 days" are supported).

You may specify multiple types using the | character or by sending an array.

Key Values

It is possible to set what values are supported by a key. This is mostly useful for enum columns.

Custom Operators

What if you wanted to support SQL LIKE as an operator? You can register a custom operator.

• $operators->add($filterQOperator, $sqlOperator)
• $filterQOperator should match this regex: [!@#$%^&*~`?]{1,2}. In simple terms, you can use these special characters (! @ # $ % ^ & * ~ ?) one or two times as an operator.
• $sqlOperator is its corresponding SQL operator.

Let's see an example.

This will create the following SQL query

Removing an operator

If you don't want one of the default operators, you can remove it using $operators->remove($operator).

Advanced Operators

Not all operators work similar to LIKE. For example, MYSQL's MATCH AGAINST. Here's how to add an advanced operator like that.

Important: Use the where/orWhere, whereRaw/orWhere correctly as shown in the example below. Otherwise, logic may not work as expected.

Exceptions Handling

It is advised to use FilterQ with proper exception handling.

FilterQ can throw 3 errors:

• Hyvor\FilterQ\Exceptions\FilterQException
• Hyvor\FilterQ\Exceptions\ParserException - when there's an error parsing the FilterQ Expression.
• Hyvor\FilterQ\Exceptions\InvalidValueException - when an invalid value is used as per defined by key value types

The last two errors extend the FilterQException error, so catching the first one is enough. All errors have a "safe" message (English only), which can be displayed back to users for API debugging purposes.