Published 10:00 31st May 2022
Query filter... a familiar problem when developing a system. But when starting to code, many familiar questions arise in every developer in general: "Where should I put this query logic? How should I manage it for ease of use?". Honestly, for each project I develop, I write in a different style, based on the experience of previous projects to create. And every time I start a new project, I ask myself the same question this time, how do I arrange the query filter! This article can be considered as a step-by-step development of a query filter system, with corresponding problems.
At the time of writing, I am using Laravel 9, on PHP 8.1 and MySQL 8. I believe tech-stack is not a significant problem, here we focus mainly on building a Query Filter system. In this article, I will demo building a filter for the users table.
<?php
use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;
return new class extends Migration
{
/**
* Run the migrations.
*
* @return void
*/
public function up()
{
Schema::create('users', function (Blueprint $table) {
$table->id();
$table->string('name');
$table->string('email')->unique();
$table->string('gender', 10)->nullable()->index();
$table->boolean('is_active')->default(true)->index();
$table->boolean('is_admin')->default(false)->index();
$table->timestamp('birthday')->nullable();
$table->timestamp('email_verified_at')->nullable();
$table->string('password');
$table->rememberToken();
$table->timestamps();
});
}
/**
* Reverse the migrations.
*
* @return void
*/
public function down()
{
Schema::dropIfExists('users');
}
}
In addition, I also use Laravel Telescope to easily monitor the query.
In the first days of learning to use Laravel, I often directly call the filter right at the controller. Simple, no magic, easy to understand, but this way has problems:
<?php
namespace App\Http\Controllers;
use App\Models\User;
use Illuminate\Http\Request;
class UserController extends Controller
{
public function __invoke(Request $request)
{
// /users?name=ryder&email=hartman&gender=male&is_active=1&is_admin=0&birthday=2014-11-30
$query = User::query();
if ($request->has('name')) {
$query->where('name', 'like', "%{$request->input('name')}%");
}
if ($request->has('email')) {
$query->where('email', 'like', "%{$request->input('email')}%");
}
if ($request->has('gender')) {
$query->where('gender', $request->input('gender'));
}
if ($request->has('is_active')) {
$query->where('is_active', $request->input('is_active') ? 1 : 0);
}
if ($request->has('is_admin')) {
$query->where('is_admin', $request->input('is_admin') ? 1 : 0);
}
if ($request->has('birthday')) {
$query->whereDate('birthday', $request->input('birthday'));
}
return $query->paginate();
// select * from `users` where `name` like '%ryder%' and `email` like '%hartman%' and `gender` = 'male' and `is_active` = 1 and `is_admin` = 0 and date(`birthday`) = '2014-11-30' limit 15 offset 0
}
}
To be able to hide the logic during the filter, let's try using Laravel's Local Scope. Turn queries into function scopes in the User model:
// User.php
public function scopeName(Builder $query): Builder
{
if (request()->has('name')) {
$query->where('name', 'like', "%" . request()->input('name') . "%");
}
return $query;
}
public function scopeEmail(Builder $query): Builder
{
if (request()->has('email')) {
$query->where('email', 'like', "%" . request()->input('email') . "%");
}
return $query;
}
public function scopeGender(Builder $query): Builder
{
if (request()->has('gender')) {
$query->where('gender', request()->input('gender'));
}
return $query;
}
public function scopeIsActive(Builder $query): Builder
{
if (request()->has('is_active')) {
$query->where('is_active', request()->input('is_active') ? 1 : 0);
}
return $query;
}
public function scopeIsAdmin(Builder $query): Builder
{
if (request()->has('is_admin')) {
$query->where('is_admin', request()->input('is_admin') ? 1 : 0);
}
return $query;
}
public function scopeBirthday(Builder $query): Builder
{
if (request()->has('birthday')) {
$query->where('birthday', request()->input('birthday'));
}
return $query;
}
// UserController.php
public function __invoke(Request $request)
{
// /users?name=john&email=desmond&gender=female&is_active=1&is_admin=0&birthday=2015-04-11
$query = User::query()
->name()
->email()
->gender()
->isActive()
->isAdmin()
->birthday();
return $query->paginate();
// select * from `users` where `name` like '%john%' and `email` like '%desmond%' and `gender` = 'female' and `is_active` = 1 and `is_admin` = 0 and `birthday` = '2015-04-11' limit 15 offset 0
}
With this setup, we have moved most of the database operations to the model class, but the code repetition is quite a lot. Example 2 scope filters for name and email are the same, same for gender birthday and is_active is_admin groups. We will approach grouping similar query function.
// User.php
public function scopeRelativeFilter(Builder $query, $inputName): Builder
{
if (request()->has($inputName)) {
$query->where($inputName, 'like', "%" . request()->input($inputName) . "%");
}
return $query;
}
public function scopeExactFilter(Builder $query, $inputName): Builder
{
if (request()->has($inputName)) {
$query->where($inputName, request()->input($inputName));
}
return $query;
}
public function scopeBooleanFilter(Builder $query, $inputName): Builder
{
if (request()->has($inputName)) {
$query->where($inputName, request()->input($inputName) ? 1 : 0);
}
return $query;
}
// UserController.php
public function __invoke(Request $request)
{
// /users?name=john&email=desmond&gender=female&is_active=1&is_admin=0&birthday=2015-04-11
$query = User::query()
->relativeFilter('name')
->relativeFilter('email')
->exactFilter('gender')
->booleanFilter('is_active')
->booleanFilter('is_admin')
->exactFilter('birthday');
return $query->paginate();
// select * from `users` where `name` like '%john%' and `email` like '%desmond%' and `gender` = 'female' and `is_active` = 1 and `is_admin` = 0 and `birthday` = '2015-04-11' limit 15 offset 0
}
At this point we have grouped most of the duplicates. However, it is a bit difficult to remove the if statement or extend these filters to another model. We are looking for a way to completely solve this problem.
Pipeline design pattern is a design pattern that provides the ability to build and execute a sequence of actions step-by-step. Laravel has built-in Pipeline that makes it easy for us to apply this design pattern in action, but for some reason it is not listed on the official documentation. Laravel itself also applies Pipeline to the middleware between Request and Response. The most basic, to use Pipeline in Laravel, we can use like this
app(\Illuminate\Pipeline\Pipeline::class)
->send($intialData)
->through($pipes)
->thenReturn(); // data with pipes applied
For our problem, it is possible to pass an intial query User:query() to the pipeline, go through the filter steps, and return a query builder with the filters applied.
// UserController
public function __invoke(Request $request)
{
// /users?name=john&email=desmond&gender=female&is_active=1&is_admin=0&birthday=2015-04-11
$query = app(Pipeline::class)
->send(User::query())
->through([
// filters
])
->thenReturn();
return $query->paginate();
Now we need to build the pipe filters:
// File: app/Models/Pipes/RelativeFilter.php
<?php
namespace App\Models\Pipes;
use Illuminate\Database\Eloquent\Builder;
class RelativeFilter
{
public function __construct(protected string $inputName)
{
}
public function handle(Builder $query, \Closure $next)
{
if (request()->has($this->inputName)) {
$query->where($this->inputName, 'like', "%" . request()->input($this->inputName) . "%");
}
return $next($query);
}
}
// File: app/Models/Pipes/ExactFilter.php
<?php
namespace App\Models\Pipes;
use Illuminate\Database\Eloquent\Builder;
class ExactFilter
{
public function __construct(protected string $inputName)
{
}
public function handle(Builder $query, \Closure $next)
{
if (request()->has($this->inputName)) {
$query->where($this->inputName, request()->input($this->inputName));
}
return $next($query);
}
}
//File: app/Models/Pipes/BooleanFilter.php
<?php
namespace App\Models\Pipes;
use Illuminate\Database\Eloquent\Builder;
class BooleanFilter
{
public function __construct(protected string $inputName)
{
}
public function handle(Builder $query, \Closure $next)
{
if (request()->has($this->inputName)) {
$query->where($this->inputName, request()->input($this->inputName) ? 1 : 0);
}
return $next($query);
}
}
// UserController
public function __invoke(Request $request)
{
// /users?name=john&email=desmond&gender=female&is_active=1&is_admin=0&birthday=2015-04-11
$query = app(Pipeline::class)
->send(User::query())
->through([
new \App\Models\Pipes\RelativeFilter('name'),
new \App\Models\Pipes\RelativeFilter('email'),
new \App\Models\Pipes\ExactFilter('gender'),
new \App\Models\Pipes\BooleanFilter('is_active'),
new \App\Models\Pipes\BooleanFilter('is_admin'),
new \App\Models\Pipes\ExactFilter('birthday'),
])
->thenReturn();
return $query->paginate();
// select * from `users` where `name` like '%john%' and `email` like '%desmond%' and `gender` = 'female' and `is_active` = 1 and `is_admin` = 0 and `birthday` = '2015-04-11' limit 15 offset 0
}
By moving each query logic to a separate class, we have unlocked customization possibilities using OOP including polymorphism, inheritance, encapsulation, abstraction. For example, you see in the handle function of pipe, only the logic in if statement is different, I will separate and abstract it by creating an abstract class BaseFilter
//File: app/Models/Pipes/BaseFilter.php
<?php
namespace App\Models\Pipes;
use Illuminate\Database\Eloquent\Builder;
abstract class BaseFilter
{
public function __construct(protected string $inputName)
{
}
public function handle(Builder $query, \Closure $next)
{
if (request()->has($this->inputName)) {
$query = $this->apply($query);
}
return $next($query);
}
abstract protected function apply(Builder $query): Builder;
}
// BooleanFilter
class BooleanFilter extends BaseFilter
{
protected function apply(Builder $query): Builder
{
return $query->where($this->inputName, request()->input($this->inputName) ? 1 : 0);
}
}
// ExactFilter
class ExactFilter extends BaseFilter
{
protected function apply(Builder $query): Builder
{
return $query->where($this->inputName, request()->input($this->inputName));
}
}
// RelativeFilter
class RelativeFilter extends BaseFilter
{
protected function apply(Builder $query): Builder
{
return $query->where($this->inputName, 'like', "%" . request()->input($this->inputName) . "%");
}
}
Now that our Filter is intuitive and highly reusable, easy to implement and even extend, just create a pipe, extends BaseFilter and declare the function apply and it's ready to apply into Pipeline.
At this point, we will try to hide the Pipeline on the controller, to make our code cleaner, by creating a scope that calls the Pipeline inside the Model.
// User.php
public function scopeFilter(Builder $query)
{
$criteria = $this->filterCriteria();
return app(\Illuminate\Pipeline\Pipeline::class)
->send($query)
->through($criteria)
->thenReturn();
}
public function filterCriteria(): array
{
return [
new \App\Models\Pipes\RelativeFilter('name'),
new \App\Models\Pipes\RelativeFilter('email'),
new \App\Models\Pipes\ExactFilter('gender'),
new \App\Models\Pipes\BooleanFilter('is_active'),
new \App\Models\Pipes\BooleanFilter('is_admin'),
new \App\Models\Pipes\ExactFilter('birthday'),
];
}
// UserController.php
public function __invoke(Request $request)
{
// /users?name=john&email=desmond&gender=female&is_active=1&is_admin=0&birthday=2015-04-11
return User::query()
->filter()
->paginate()
->appends($request->query()); // append all current queries into pagination links
// select * from `users` where `name` like '%john%' and `email` like '%desmond%' and `gender` = 'female' and `is_active` = 1 and `is_admin` = 0 and `birthday` = '2015-04-11' limit 15 offset 0
}
User now can call filter from anywhere. But other models also want to implement filter, we will create a Trait containing the scope and declare the pipes participating in the filter process inside the model.
// User.php
use App\Models\Concerns\Filterable;
class User extends Authenticatable {
use Filterable;
protected function getFilters()
{
return [
new \App\Models\Pipes\RelativeFilter('name'),
new \App\Models\Pipes\RelativeFilter('email'),
new \App\Models\Pipes\ExactFilter('gender'),
new \App\Models\Pipes\BooleanFilter('is_active'),
new \App\Models\Pipes\BooleanFilter('is_admin'),
new \App\Models\Pipes\ExactFilter('birthday'),
];
}
// the rest of code
// File: app/Models/Concerns/Filterable.php
namespace App\Models\Concerns;
use Illuminate\Database\Eloquent\Builder;
use Illuminate\Pipeline\Pipeline;
trait Filterable
{
public function scopeFilter(Builder $query)
{
$criteria = $this->filterCriteria();
return app(Pipeline::class)
->send($query)
->through($criteria)
->thenReturn();
}
public function filterCriteria(): array
{
if (method_exists($this, 'getFilters')) {
return $this->getFilters();
}
return [];
}
}
We have solved the divide and conquer problem, each file, each class, and each function now have clear responsibilities. The code is also clean, intuitive and much easier to reuse, isn't it! I will put the code of the whole process Demo this post here
The above is a part of the journey that I went through to build an advanced Query Filter system, and at the same time introduces you to some Laravel programming approaches such as Local Scope and especially Pipeline design pattern. To quickly and easily apply this setup to a new Project, you can use the package Pipeline Query Collection, which includes a set of pre-built pipes makes it easy to install and use. Hope everyone will support it!