DEV Community

Fouladgar.dev
Fouladgar.dev

Posted on • Edited on

Making the advanced search query with Eloquent-Builder in Laravel

Unsplash @aginsbrook
We needed an advanced search system in the recent project. This system included many filters that required a flexible and scalable search system.
I decided to implement a package for this system that you can see it in the GitHub and use it in your project.

What's the problem?

The problem is that you will encounter with a set of filters that you must check for a lot of conditions to add to the query.

Writing a lot of terms will surely reduce the readability of your code and slow down the development process.

Also, you can only use the filters and terms in the same scope and they will not be reusable.

But the Solution

You must Refactor your code!

To solve this problem, you need to Refactor your code by replacing many of your conditionals with Polymorphism.

Click here to learn more about this design pattern.

Practical Example:

Suppose we want to get the list of the users with the requested parameters as follows:

http://dev.to/api/users/search?age_more_than=25&gender=male&has_published_post=true
Enter fullscreen mode Exit fullscreen mode

The Requested parameter will be as follows:

[ 
  'age_more_than' => '25',
  'gender' => 'male',
  'has_published_post' => 'true',
]
Enter fullscreen mode Exit fullscreen mode

In a common implementation, following code will be expected:

We check out a condition for each request parameter.

In the future, we will have more parameters that should be checked and applied in the code above that causes us to have a dirty code.

Use the Eloquent-Builder

After installing the package presented,change your code as follows:

You just send the model and parameters to ‘to’ method.Then, you need to define the filter for each parameter that you want to add to the query.

So easy!

Defining a filter

For example, I’ll implement one of the above example filters. Follow the example below:


For more details check out the GitHub repository.

GitHub logo mohammad-fouladgar / eloquent-builder

This package provides an advanced filter for Laravel model based on incoming requets.

Provides a Eloquent query builder for Laravel

alt text

Latest Version on Packagist Test Status Code Style Status Total Downloads

This package allows you to build eloquent queries, based on incoming request parameters. It greatly reduces the complexity of the queries and conditions, which will make your code clean and maintainable.

Version Compatibility

Laravel EloquentBuilder
11.0.x 5.x.x
10.0.x 4.2.x
9.0.x 4.0.x
6.0.x to 8.0.x 3.0.x
5.0.x 2.2.2

Basic Usage

Suppose you want to get the list of the users with the requested parameters as follows:

//Get api/user/search?age_more_than=25&gender=male&has_published_post=true
[
    'age_more_than'  => '25',
    'gender'         => 'male',
    'has_published_post' => true,
]
Enter fullscreen mode Exit fullscreen mode

In a common implementation, following code will be expected:

<?php
namespace App\Http\Controllers;

use App\Models\User;
use Illuminate\Http\Request;

class UserController extends Controller
{
    public function index(Request $request)
    {
        $users = User::where(
Enter fullscreen mode Exit fullscreen mode

Good luck and thank you for sharing your valuable time with me.

Happy Coding!

Top comments (29)

Collapse
 
farrukh_uet profile image
Farrukh • Edited

Hi,
First of all thanks for your easy and great solution.
I found many solutions for eloquent search filters, but this is discrete :).

I felt that relational object filters were not discussed.
So here is my code for eloquent relational search, hope it will also help some one.

For relational object follow these steps:

I have two tables Profiles and Leads.
Each Profile has some Leads against it.

This is my coding style, please change this according to your code ethics.

// @$Profile is the instance of collection in my Controller function

$Profiles = $this->Profiles;
$Profiles= $this->ApplyFilters($Request, $Profiles)->get();

// Apply Filters to relational models

private function ApplyFilrers($Request, $Profiles){

foreach(request()->all() as $Key=>$Value){
// if($Key == “Category”){
$Profiles = $Profiles->whereHas(‘Leads’, function($Profile) use($Key, $Value){
// I have a CategoryFilter in my EloquentFilters\Leads
$Profile = EloquentBuilder::to($Profile, [ $Key=>$Value]);
});
//}
}

return $Profiles;

}

Collapse
 
mohammadfouladgar profile image
Fouladgar.dev

Thanks you for your kind words ;)

It was an interesting example.

Thank you for sharing.

Collapse
 
farrukh_uet profile image
Farrukh • Edited

Also if you want to implement a filter for a columns against multiple values.
For example following is the array you mentioned in example, but with gender filter as an array.

[
'age_more_than' => '25',
'gender' => ['male','female','custom']
'has_published_post' => 'true',
]

class GenderFilter extends Filter
{

public function apply(Builder $builder, $value): Builder
{

    if(is_array($value)){
        return $builder->whereIn('gender', $value);
    }

    return $builder->where('gender', '=', $value);
}

}

Thread Thread
 
mohammadfouladgar profile image
Fouladgar.dev

Nice.

Type of the value parameter is mixed and you can set it to any value based on your requirement.

Thanks Dear @Farrukh

Collapse
 
farrukh_uet profile image
Farrukh

Thanks for your appreciation.

Thread Thread
 
farrukh_uet profile image
Farrukh

Hi,
Can we do sorting for eloquent relations?

Thread Thread
 
mohammadfouladgar profile image
Fouladgar.dev

Yes,you can.
EloquentBuilder return an instance of Builder:

return EloquentBuilder::to(Blog::class,$filters)->with('latestPost')->OrderByDesc('latestPost.created_at')->get();
Thread Thread
 
farrukh_uet profile image
Farrukh

Thanks a lot for your reply.

I did not try it yet. Hope it will work as I wish.

Regards

Thread Thread
 
farrukh_uet profile image
Farrukh

Hi,

I used laravel debugger, when I used Eloquent Builder it shows me a (lot) number of queries.

is it normal or there is some method to reduce that

Regards
Farrukh

Thread Thread
 
mohammadfouladgar profile image
Fouladgar.dev

Hi,

The EloquentBuilder reduces the complexity of the queries and conditions of the code.Number of queries is not related to it.

Good luck

Thread Thread
 
farrukh_uet profile image
Farrukh

Thanks a lot dear

Collapse
 
nanadjei2 profile image
Bill Gates • Edited

Nice work man. You made a typo. According to the docs on GitHub github.com/mohammad-fouladgar/eloq...,

class AgeMoreThanFilter should extend Filter and not implement Filter

And EloquentBuilder::to() method should not be called statically. Am sure the package has been updated.

Collapse
 
mohammadfouladgar profile image
Fouladgar.dev • Edited

Thanks Bill, Yes The package has been updated.

Please read this article : dev.to/mohammadfouladgar/laravel-m...

Collapse
 
cpekas profile image
cpekas

thank you for your great solution

Collapse
 
mohammadfouladgar profile image
Fouladgar.dev

You're welcome. Good luck...

Collapse
 
redon92 profile image
Redon

Seriously? Imagine if you have 15-20 filters, you will repeat the queries with if loop 20 times?

Collapse
 
mohammadfouladgar profile image
Fouladgar.dev

I think it's better than using complex terms. In this way we have better management of each filter.

Also, We observation to the single responsibility principle.

if you have a better way, please suggest. thanks a lot for your notice.

Collapse
 
redon92 profile image
Redon

You can build the query with normal mysql syntax, then if you use a form, give the form name of the database field that you want to compare, and the value of the form input is the value you want to search for example 3, or some name. Then when the value returned is null, you normally don't take the field into the sql string. Its just some formatting you have to use in the controller, and its all automated, if the main purpose is directory listing or similar stuff

Thread Thread
 
mohammadfouladgar profile image
Fouladgar.dev

I suggest using the package once.
The query goes to the database only once.

Collapse
 
cyberfly profile image
Muhammad Fathur Rahman • Edited

Nice post. Btw your before and after example is the same, took me sometimes to realize this.

Edit : After sign in, the post display correct before and after, maybe a bug

Collapse
 
mohammadfouladgar profile image
Fouladgar.dev

Thanks!

I hope useful for you.

maybe a bug

Maybe...,i don't know

Collapse
 
rakeshmaity271 profile image
honeycrisp

Thank God, Love It

Collapse
 
mohammadfouladgar profile image
Fouladgar.dev

You're welcome! I'm glad to hear that.

Collapse
 
robsontenorio profile image
Robson Tenório

Nice post! And how about this?

github.com/spatie/laravel-query-bu...

Collapse
 
mohammadfouladgar profile image
Fouladgar.dev

Thanks so much.

That package is also good.

My goal is to use it more easily and avoid any complexity too.

Also, for the better development of the source, used from design patterns such as the factory method and other concepts of OOP.

Collapse
 
stevejohnme007 profile image
stevejohnme007

Nice post!
Can we combine two or more filter classes to single one.

Collapse
 
mohammadfouladgar profile image
Fouladgar.dev • Edited

If some filters have be a single responsibility you can manage them in a single filter class.Otherwise, you should define a filter-class for each filter argument.

Collapse
 
mohammadfouladgar profile image
Fouladgar.dev

You are welcome, Thank you 👍

Collapse
 
mohammadfouladgar profile image
Fouladgar.dev • Edited

@bpedroza Thank you for your noticing.

This block updated in GitHub repository.