Simplify Eloquent Query Filtering in Laravel with Eloquent Filter

Introduction

Filtering data in Laravel applications can become repetitive and messy, especially when dealing with complex queries. If you have ever done an advanced search page or reporting system, you know this fact managing this feature is difficult.

Mention that Eloquent Filter is a package that simplifies this process by providing a clean and reusable way to filter Eloquent models.

The link to the checkout is GitHub Eloquent Filter repository.

What is Eloquent Filter?

Explain what the package does: It allows you to filter Eloquent models based on query parameters. It helps keep your controllers clean by moving filtering logic into dedicated filter classes.

It supports dynamic filtering, making it easy to add or modify filters without changing your codebase extensively.

Highlight its key features:

• Easy to install and configure.

• Supports complex filtering conditions.

• Works seamlessly with Laravel’s Eloquent ORM.

• Supports query builder.

Installation

Run the Composer command

$ composer require mehdi-fathi/eloquent-filter

2- Add eloquentFilter\ServiceProvider::class to provider app.php

   'providers' => [
     /*
      * Package Service Providers...
      */
      eloquentFilter\ServiceProvider::class
   ]

• In the latest Laravel version add it to providers.php:

    return [
        App\Providers\AppServiceProvider::class,
        eloquentFilter\ServiceProvider::class
    ];

3- Add Facade 'EloquentFilter' => eloquentFilter\Facade\EloquentFilter::class to aliases app.php

'alias' => [
  /*
   * Facade alias...
   */
    'EloquentFilter' => eloquentFilter\Facade\EloquentFilter::class,
],

Basic Usage

Add the Filterable trait to your models and set fields in the whitelist array in which you will want to use of
filter.

• You can override this method in your models as well.

use eloquentFilter\QueryFilter\ModelFilters\Filterable;

class User extends Model
{
    use Filterable;

    private static $whiteListFilter =[
        'id',
        'username',
        'email',
        'created_at',
        'updated_at',
    ];
}

• You can set * char for that filter in all fields.

A simple implementation with Eloquent Filter

Eloquent Filter can help you to manage these features. Just you will set the query string to work with that.
It would make your own query automatically and systematically while you can control them.

Right after installing Eloquent Filter, the request URI would be like this:

/users/list?age_more_than[operator]=>&age[value]=35&gender=male&created_at[operator]==>&created_at[value]=25-09-2019

And in the Controller, You just need that one line:

/**
 * Class UsersController.
 */

namespace App\Http\Controllers;

use App\User;

class UsersController
{
    public function list()
    {
        return User::filter()->get();
    }
}

• Eloquent Filter is supporting query builder

Usage is just extremely like model you need to use filter as a method. Obviously, there's no need for any change.

 DB::table('users')->filter();

Conditions Guidance Table

• To better understand this, I provided a table of all conditions and samples. It represents how eloquent filter detect params and each param what query would make.

Condition Name	Eloquent Method	Param	Example	Eloquent	DB
WhereCustomCondition			Declared custom method of Model	✅	❌
SpecialCondition		f_params[limit]=10	support f_params, e.g: limit and order	✅	✅
WhereBetweenCondition	whereBetween	created_at[start]=2016/05/01 &created_at[end]=2017/10/01	whereBetween( 'created_at', [{start},{end}])	✅	✅
WhereByOptCondition	where	count_posts[operator]=>& count_posts[value]=35	where('count_posts', ">", $value)	✅	✅
WhereLikeCondition	where	first_name[like]=John	where('first_name', 'like', $value)	✅	✅
WhereInCondition	whereIn	username[]=David& username[]=John12	whereIn('username', $value)	✅	✅
WhereOrCondition	orWhere	username=Bill& or[username]=James	orWhere('username', $value)	✅	✅
WhereHas	WhereHas	posts[title]=sport one	whereHas('posts', function ($q) {$q->where('title', $value)});	✅	✅
WhereDoesntHaveCondition	whereDoesntHave	doesnt_have=category	doesntHave($value)	✅	✅
WhereDateCondition	whereDate	created_at=2024-09-01	whereDate('created_at', $value)	✅	✅
WhereYearCondition	whereYear	created_at[year]=2024	whereYear('created_at', $value)	✅	✅
WhereMonthCondition	whereMonth	created_at[month]=3	whereMonth('created_at', $value)	✅	✅
WhereDayCondition	whereDay	created_at[day]=15	whereDay('created_at', $value)	✅	✅
WhereNullCondition	whereNull	username[null]=true	whereNull('username')	✅	✅
WhereNotNullCondition	whereNotNull	username[not_null]=true	whereNotNull('username')	✅	✅
WhereCondition	where	username=Mehdi	where('username', $value)	✅	✅

Simple Examples

You just pass data form as query string. For example:

Simple Where

/users/list?email=[e-mail]

SELECT ... WHERE ... email = '[e-mail]'

/users/list?first_name=mehdi&last_name=fathi

SELECT ... WHERE ... first_name = 'mehdi' AND last_name = 'fathi'

• If you send date format Y-m-d we will work like WhereDate() method Laravel.

/users/list?created_at=2024-09-01

SELECT ... WHERE ... strftime('%Y-%m-%d', "created_at") = cast(2024-09-01 as text)

Where In

This example make method whereIn.

/users/list?username[]=ali&username[]=ali22&family=ahmadi

SELECT ... WHERE ... username in ('ali','ali22') AND family = 'ahmadi'

OrWhere

This example make method orWhere().

/users/list?name=mehdi&username=fathi&or[username]=ali

SELECT ... WHERE ... name = 'mehdi' AND username = 'fathi' or username = 'ali'

Where like

If you are going to make a query by like conditions. You can do that by this example.

/users/list?first_name[like]=%John%

SELECT ... WHERE ... first_name LIKE '%John%'

Where by operator

You can set any operator mysql in the queries string.

/users/list?count_posts[operator]=>&count_posts[value]=35

SELECT ... WHERE ... count_posts > 35

/users/list?username[operator]=!=&username[value]=ali

SELECT ... WHERE ... username != 'ali'

/users/list?count_posts[operator]=<&count_posts[value]=25

SELECT ... WHERE ... count_posts < 25

Where the nested relations Model

You can set all nested relations in the query string just via the array of query string. Imagine, the user model has a
relation with posts. And posts table has a relation with orders table.

You can make query conditions by set posts[count_post] and posts[orders][name] in the query string.

• Just be careful you must set posts.count_post and posts.orders.name in the User model.

use eloquentFilter\QueryFilter\ModelFilters\Filterable;

class User extends Model
{
    use Filterable;

    private static $whiteListFilter =[
        'username',
        'posts.count_post',
        'posts.category',
        'posts.orders.name',
    ];

    /**
     * @return \Illuminate\Database\Eloquent\Relations\belongsTo
     */
    public function posts()
    {
        return $this->belongsTo('Models\Post');
    }

}

/users/list?posts[count_post]=876&username=mehdi

select * from "users" where exists 
         (select * from "posts" where "posts"."user_id" = "users"."id" 
         and "posts"."count_post" = 876)
         and "username" = "mehdi"

• The above example is the same code that you used without the eloquent filter. Check it under code. It's not amazing?

$builder = (new User())->with('posts');
        $builder->whereHas('posts', function ($q) {
            $q->where('count_post', 876);
        })->where('username','mehdi');

Where array the nested relation Model

You can pass array to make whereIn condition.

/users/list?posts[category][]=php&posts[category][]=laravel&posts[category][]=jquery&username=mehdi

select * from "users" where exists 
         (select * from "posts" where 
         "posts"."category" in ('php','laravel','jquery') )
         and "username" = "mehdi"

Doesnthave Where (new feature)

/tags/list?doesnt_have=category

select * from "tags" where not exists (select * from "categories" where "tags"."foo_id" = "categories"."id")'

• To fetching those data that doesn't have any relationship with the model as the same Doesnthave method worked.

*Special Params*

You can set special params limit and orderBy in the query string to make a query by that.

/users/list?f_params[limit]=1

SELECT ... WHERE ... order by `id` desc limit 1 offset 0

/users/list?f_params[orderBy][field]=id&f_params[orderBy][type]=ASC

SELECT ... WHERE ... order by `id` asc

/users/list?f_params[orderBy][field]=id,count_posts&f_params[orderBy][type]=ASC

SELECT ... WHERE ...  order by `id` asc, `count_posts` asc

Where between

If you are going to make a query based on date, You must fill keys, start, and end in the query string.
Hence You can set it as a query string. These params are used for the filter by date.

/users/list?created_at[start]=2016/05/01&created_at[end]=2017/10/01

SELECT ... WHERE ... created_at BETWEEN '2016/05/01' AND '2017/10/01'

*Advanced Where*

/users/list?count_posts[operator]=>&count_posts[value]=10&username[]=ali&username[]=mehdi&family=ahmadi&created_at[start]=2016/05/01&created_at[end]=2020/10/01
&f_params[orderBy][field]=id&f_params[orderBy][type]=ASC

select * from `users` where `count_posts` > 10 and `username` in ('ali', 'mehdi') and 
`family` = ahmadi and `created_at` between '2016/05/01' and '2020/10/01' order by 'id' asc limit 10 offset 0

Therefore, fields of query string are same rows table database in $whiteListFilter in your model or declare the method in your model as override method.
The overridden method can be considered a custom query filter.

For more information about the feature's Eloquent Filter, you'd better see the GitHub Eloquent Filter repository.