search

hyvor / laravel-filterq

Advanced filtering for Laravel APIs
MIT License
6 stars 0 forks source link
laravel sql

readme

Introduction

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

name=starter&(type=image|type=video)

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

->where(function($query) { // wrapper

    $query->where('name', 'starter')
        ->where(function($query) {
            $query->where('type', 'image')
                ->orWhere('type', 'video');
        });

})

or in SQL:

WHERE ( name = 'starter' AND ( type = 'image' OR type = 'video') )

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

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.

A condition has three parts:

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.

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

Values

Basic Usage

composer require hyvor/laravel-filterq
use Hyvor\FilterQ\Facades\FilterQ;

$query = FilterQ::expression('id=100|slug=hello')
    ->builder(Post::class)
    ->keys(function($keys) {
        $keys->add('id')->column('posts.id');
        $keys->add('slug');
        $keys->add('author.name')
            ->column('authors.name')
            ->join('authors', 'authors.id', '=', 'posts.author_id', 'left');
    })
    ->addWhere();

$posts = $query
    ->limit(25)
    ->orderBy('id', 'DESC')
    ->get();

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.


class MyController {

    public function handle(Request $request) {

        $filter = $request->input('filter');

        FilterQ::expression($expression)
            ->builder(Post::class)
            // other methods...

    }

}

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.

FilterQ::expression(...)
    ->builder(Post::class)
    ->keys(function ($keys) {
        $keys->add('id')->column('posts.id');
        $keys->add('slug');
    });

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.

$posts = FilterQ::expression(...)
    ->builder(...)
    ->keys(...)
    ->addWhere()
    ->limit(...)
    ->orderBy(...)
    ->get();

Joins

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

FilterQ::expression('author.name=hyvor')
    ->builder(Post::class)
    ->keys(function($keys) {
        $keys->add('author.name')
            ->column('authors.name')
            ->join('authors', 'authors.id', '=', 'posts.author_id');
    })
    ->addWhere();

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:

select * from "posts" 
inner join "authors" on "authors"."id" = "posts"."author_id" 
where ("authors"."name" = 'hyvor')

->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('authors', 'authors.id', '=', 'posts.author_id', 'left')

JOIN with a callback

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


// join with WHERE
$keys->add(...)
    ->join(function($query) {
        $query->join('authors', function($join) {
            $join->on('authors.id', '=', 'posts.author_id')
                ->where('authors.status', '!=', 'active');
        });
    });

// subquery JOINS
$keys->add(...)
    ->join(function($query) {
        $query->joinSub(...);
    });

Key Operators

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

FilterQ::expression(...)
    ->builder(...)
    ->keys(function($keys) {
        // only these operators will be allowed (comma-separated string)
        $keys->add('id')->operators('=,>,<');

        // or use an array
        $keys->add('slug')->operators(['=', '!=']);

        // exclude operators (use true as the second param)
        $keys->add('age')->operators('>', true);
    })
    ->addWhere();

Key Value Types

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

FilterQ::expression(...)
    ->builder(...)
    ->keys(function($keys) {

        $keys->add('id')->valueType('integer');
        $keys->add('name')->valueType('string');
        $keys->add('description')->valueType('string|null'); // |-seperated types
        $keys->add('title')->valueType(['string', 'null']); // or an array
        $keys->add('created_at')->valueType('date');

    });

The valueType method supports the following types:

Scalar:

Special:

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

$keys->add('created_at')->valueType('date|null');
// or
$keys->add('created_at')->valueType(['date', 'null']);

Key Values

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

FilterQ::expression(...)
    ->builder(...)
    ->keys(function($keys) {

        // allows either published or draft
        $keys->add('status')->values(['published', 'draft']); 

        // only 200 is allowed
        $keys->add('id')->values(200);

    });

Custom Operators

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

FilterQ::expression(...)
    ->builder(...)
    ->keys(...)
    ->operators(function ($operators) {
        $operators->add('~', 'LIKE');
    });

Let's see an example.

FilterQ::expression("title~'Hello%'")
    ->builder(Post::class)
    ->keys(function($keys) {
        $keys->add('title');
    })
    ->operators(function($operators) {
        $operators->add('~', 'LIKE');
    })
    ->addWhere();

This will create the following SQL query

select * from "posts" where ("title" LIKE 'Hello%')

Removing an operator

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

->operators(function($operators) {
    $operators->remove('>');
});

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.

FilterQ::expression(...)
    ->builder(...)
    ->keys(...)
    ->operators(function($operators) {
        $operators->add('!', function($query, $whereType, $value) {

            /**
             * $query - use it to add WHERE statements
             * $whereType - and|or (current logical scope)
             * $value - value in the FilterQ expression
             */

            // THIS IS IMPORTANT!!!
            $rawWhere = $whereType === 'and' ? 'whereRaw' : 'orWhereRaw';

            // $query->whereRaw()
            $query->{$rawWhere}('MATCH (title) AGAINST (?)', [$value]);

        });
    })
    ->addWhere();

Exceptions Handling

It is advised to use FilterQ with proper exception handling.

FilterQ can throw 3 errors:

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.

try {

    $postsBuilder = FilterQ::expressions(...)
        ->builder(...)
        ->keys(...)
        ->addWhere();

} catch (FilterQException $e) {
    dd($e->getMessage());
}

$posts = $postsBuilder->get();