DEV Community

Making the advanced search query with Eloquent-Builder in Laravel

Fouladgar.dev on September 05, 2018

We needed an advanced search system in the recent project. This system included many filters that required a flexible and scalable search system. ...

Read full post

Farrukh • Feb 7 '20 • 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;

}

Fouladgar.dev • Feb 8 '20

Thanks you for your kind words ;)

It was an interesting example.

Thank you for sharing.

Farrukh • Feb 8 '20 • 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);
}

}

Fouladgar.dev • Feb 9 '20

Nice.

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

Thanks Dear @Farrukh

Farrukh • Feb 8 '20

Thanks for your appreciation.

Farrukh • Feb 12 '20

Hi,
Can we do sorting for eloquent relations?

Fouladgar.dev • Feb 15 '20

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

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

Farrukh • Feb 18 '20

Thanks a lot for your reply.

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

Regards

Farrukh • Feb 18 '20

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

Fouladgar.dev • Feb 18 '20

Hi,

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

Good luck

Farrukh • Feb 18 '20

Thanks a lot dear

Bill Gates • Oct 14 '19 • 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.

Fouladgar.dev • Oct 15 '19 • Edited

Thanks Bill, Yes The package has been updated.

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

cpekas • Jun 26 '19

thank you for your great solution

Fouladgar.dev • Jun 29 '19

You're welcome. Good luck...

Redon • Aug 21 '19

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

Fouladgar.dev • Aug 25 '19

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.

Redon • Aug 25 '19

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

Fouladgar.dev • Aug 26 '19

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

Muhammad Fathur Rahman • Sep 18 '18 • 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

Fouladgar.dev • Sep 18 '18

Thanks!

I hope useful for you.

maybe a bug

Maybe...,i don't know

honeycrisp • Aug 30 '21

Thank God, Love It

Fouladgar.dev • Sep 5 '21

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

Robson Tenório • Sep 13 '18

Nice post! And how about this?

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

Fouladgar.dev • Sep 15 '18

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.

stevejohnme007 • Mar 10 '20

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

Fouladgar.dev • Mar 10 '20 • 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.

Fouladgar.dev • Aug 25 '19

You are welcome, Thank you 👍

Fouladgar.dev • Jan 4 '20 • Edited

@bpedroza Thank you for your noticing.

This block updated in GitHub repository.