Have you ever experienced make 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 that time our task will getting hard. I was searching about it. Eventually i found a clean way to implement this feature in Laravel project.
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.
Suppose we want to get the list of the users with the requested parameters as follows:
The Request parameter will be as follows:
[ 'age_more_than' => '24', 'gender' => 'male', 'created_at' => '25-09-2019', ]
Implement this feature in
We check out a condition for each request.
In the future if your project will need more filter request that time you should add many conditions to above code.Imagine some of queries maybe advanced therefore your code to be like Monster!
Everything began from just a tutorial video called
Eloquent Techniques in laracast. It was about a new way to make advanced filter by use of query string. It has separating layer filter from controller and model. It made a Eloquent query by use of query string.Advanced Eloquent rescued your readability of your code by a filter class. It was very good but you had to make a method for every conditions. It's boring for a creative developers. You don't have dynamic query and you have to write many method for per condition just in separated layer.
Imagine you will install a package composer and make query by query string without write where for every conditions.Actually you just enter query string as according to the principles of that package. Therefore the package will make every conditions by query string. If you want write custom query in separated layer you can do it. You can set fields of your model to allow build query by query string. Note that query string must be sync with 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.
Run the Composer command
$ composer require mehdi-fathi/eloquent-filter
Add Filterable trait to your models and set fields that you will want 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 = ['*'];
You just pass data blade form to query string or generate query string in controller method.For example:
/firstname.lastname@example.org SELECT ... WHERE ... email = 'email@example.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'
You can set any operator MySQL in 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
You can set special parameters
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
If you are going to make query whereBetween. You must fill keys
end in 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'
/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 same rows table database in
$whiteListFilter in your model or declare method in your model as override method. Override method can be considered custom query filter.
If you are going to make yourself query filter you can do it easily.You just make a trait and use it on model:
Note that fields of query string be same methods of trait.Use 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.
For more details check out GitHub repository
Good luck and thank you for sharing your valuable time with me. I hope Eloquent Filter is useful for your code. If you have any idea or opinion i glade to know it.