Introduction
Have you ever experienced make an advanced search page in programming? if your answer is yes maybe you know this feature has many conditions to make a query database.Especially when you are using a relational DB as MySQL in your project at that time our task will getting hard. I was searching for it. Eventually, I found a clean way to implement this feature in the Laravel project.
What's the main problem?
The main problem is that you will make many conditions for every situation. Writing a lot of terms will surely reduce the readability of your code and increase the possibility of make a mistake.
Practical example:
Suppose we want to get the list of the users with the requested parameters as follows:
http://localhost:8000/users/search?age_more_than=25&gender=male&created_at=25-09-2019
The Request parameter will be as follows:
[
'age_more_than' => '24',
'gender' => 'male',
'created_at' => '25-09-2019',
]
Implement this feature in UsersController.php
We check out a condition for each request.
In the future, if your project will need more filter requests at that time you should add many conditions to the above code. Imagine some of the queries may be advanced therefore your code to be like Monster!
Discover away
Everything began from just a tutorial video called Eloquent Techniques in laracast. It was about a new way to make an advanced filter by use of the query string. It has to separate layer filters from controller and model. It made an Eloquent query by the use of a query string. Advanced Eloquent rescued the readability of your code by a filter class. It was very good but you had to make a method for every condition. It's boring for a creative developer. You don't have a dynamic query and you have to write many methods for per condition just in a separate layer.
Eloquent Filter is your solution
Imagine you will install a package composer and make a query by query string without write where for every condition. Actually you just enter query string according to the principles of that package. Therefore the package will make every condition by the query string. If you want to write a custom query in the separated layer you can do it. You can set fields of your model to allow build queries by the query string. Note that the query string must be sync with the fields of your Model. Isn't great in your opinion !?. This package saves your time and your code. Fortunately like package eloquent-filter is rare in GitHub.
-NOTE THAT : we've released the Eloquent filter version 2 recently. We suggest to installing version 2 with the new features you can visit this link : https://dev.to/mehdifathi/introduce-eloquent-filter-2-in-laravel-12g5
Installation
Run the Composer command
$ composer require mehdi-fathi/eloquent-filter:1.6.9
Basic Usage
Add Filterable trait to your models and set fields that you will want a filter in whitelist. You can override this method in your models.
You can set * char for filter in all fields as like below example:
private static $whiteListFilter = ['*'];
Use in Controller
Done it!
Simple Example
You just pass the data blade form to query string or generate a query string in the controller method. For example:
Simple Where
/users/list?email=mehdifathi.developer@gmail.com
SELECT ... WHERE ... email = 'mehdifathi.developer@gmail.com'
/users/list?first_name=mehdi&last_name=fathi
SELECT ... WHERE ... first_name = 'mehdi' AND last_name = 'fathi'
/users/list?username[]=ali&username[]=ali22&family=ahmadi
SELECT ... WHERE ... username = 'ali' OR username = 'ali22' AND family = 'ahmadi'
Where by operator
You can set any operator MySQL in the query 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
Special Parameters
You can set special parameters limit and orderBy in query string for make 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 limit 10 offset 0
Where between
If you are going to make a query whereBetween. You must fill the keys start and end in the query string. you can set it on query string as you know.
/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
Just note that fields of query string be the same rows table database in $whiteListFilter in your model or declare the method in your model as the override method. The overriding method can be considered custom query filter.
Custom query filter
If you are going to make yourself a query filter you can do it easily. You just make a trait and use it on model:
Note that fields of query string be the same methods of a trait. Use the trait in your model:
/users/list?username_like=a
select * from `users` where `username` like %a% order by `id` desc limit 10 offset 0
You can make every filter with eloquent-filter. If you think the eloquent-filter is useful so give a ⭐️ to that by click in the link
GitHub.
For more details check out GitHub repository
Good luck and thank you for sharing your valuable time with me. I hope the Eloquent Filter is useful for your code. If you have any idea or opinion I glade to know it.
Top comments (20)
Thanks for your efforts. I'm new to laravel and didn't quite get it working. In particular the Request causes me issues. My controller is extended from Controller and has as a parameter Request. If I make all the changes as you outlined I got first of all a complain that Requests is unknown (I added the use statement for Requests back in) and thereafter the complain "Non-static method Illuminate\Http\Request::input() should not be called statically". What am I missing and what;s the solution?
Worth to mention - I'm using the latest Laravel and I'm wondering if that has already some functionality that supersedes your package?
Thanks,
Goppi
Your problem is related to laravel.please read request laravel doc.
Do you think he didn't read before askign? This is quite toxic. I'm learning Laravel myself and I had that error before as well. I read the docs, and I didn't quite understand it, or I missed the docs or etiher didn't understand the docs.
A link pointing to that, and maybe saying what a "static" method is would be more helpful.
Assume that people read the docs when they post such comments...
I have got a parameter in my API call that is not related to a table field and as such should not be used by the filter. I didn't add it to the whitelist (similar like your page parameter) but I'm getting the error "You must set flexibility in whiteListFilter in SpecialOffer\n or create a override method."
I guess I need to create an override method - how do I do that?
thanks
goppi
You must set white list array in your model.you can read this article again.
I am new to Laravel and am using your package in a school project and it works like a charm👍
I do however wanna give you a heads up about one small bug i have seemed to run into.
When combining your filter with a paginator it correctly ignores the page query parameter:
'my-url.com/api/resource?page=5'
But it doesn't seem to ignore the perPage query parameter:
'my-url.com/api/resource?page=5&perPage=100'
This causes a code 500 response.
With an error asking met to add it to the whitelist. (Laravel V7.0)
Thanks, you can use this code
$perpage = Request::input('perpage');
Request::offsetUnset('perpage');
$users = User::filter($modelFilters)->with('posts')->orderByDesc('id')->paginate($perpage,['*'],'page');
You can make every param for your self but you can unset before send to filter method model
Really nice filter we are using at my work too.
Thanks
very nice!
I tried to use it with paginate, but returned an error saying that the colums page is not on the whitelist, you know how to fix it?
Yes, you are right. I fixed it. You just run the command
composer update mehdi-fathi/eloquent-filterto update that. If you have any problem you should read the readme in Github or comment that under this post. Thanks for your Response.Illuminate \ Contracts \ Container \ BindingResolutionException
Target class [eloquentFilter] does not exist.
Do you how to fix this?
I followed your github respository install instructions
never mind.. just cache: php artisan config:cache
thankyou for this tools. i have error: Undefined type 'eloquentFilter\QueryFilter\ModelFilters\Filterable'.intelephense(1009)
in laravel 7
Please explain more for help you.
What about apply this filter on joins?
I'va installed mehdi-fathi/eloquent-filter and it works perfectly, but filter doesn't take effect where there is a join
Some idea?
This article is about the old version you should read about this feature at github.com/mehdi-fathi/eloquent-fi....
your star link github.com/mehdi-fathi/eloquent-fi... in article didnt worked
You are right I've fixed it now.
i tested the trait as firstname_like and its worked. nice. thank you