Compatible with Laravel 5.x 6.x 7.x 8.x 9.x 10.x 11.x.
You have probably faced the situation where you needed to filter your query based on given parameters in url query-string and after developing the logics, You've had such a code:
$users = User::latest();
if(request('username')) {
$users->where('username', request('username'));
}
if(request('age')) {
$users->where('age', '>', request('age'));
}
if(request('email')) {
$users->where('email', request('email'));
}
return $users->get();This works, But it's not a good practice.
When the number of parameters starts to grow, The number of these kind of if statements also grows and your code gets huge and hard to maintain.
Also it's against the Open/Closed principal of SOLID principles, Because when you have a new parameter, You need to get into your existing code and add a new logic (which may breaks the existing implementations).
So we have to design a way to make our filters logics separated from each other and apply them into the final query, which is the whole idea behind this package.
- First you need to install the package:
$ composer require mehradsadeghi/laravel-filter-querystring
- Then you should
usetheFilterQueryStringtrait in your model, And define$filtersproperty which can be consist of available filters or your custom filters.
use Mehradsadeghi\FilterQueryString\FilterQueryString;
class User extends Model
{
use FilterQueryString;
protected $filters = [];
...
}- You need to use
filter()method in your eloquent query. For example:
User::select('name')->filter()->get();For the purpose of explaining each method, Imagine we have such data in our users table:
| id | name | username | age | created_at | |
|---|---|---|---|---|---|
| 1 | mehrad | mehrad@example.com | mehrad123 | 20 | 2020-09-01 |
| 2 | reza | reza@example.com | reza123 | 20 | 2020-10-01 |
| 3 | hossein | hossein@example.com | hossein123 | 22 | 2020-11-01 |
| 4 | dariush | dariush@example.com | dariush123 | 22 | 2020-12-01 |
And assume our query is something like this:
User::filter()->get();Sort is the equivalent to order by sql statement which can be used flexible in FilterQueryString:
Conventions:
?sort=field
?sort=field,sort_type
?sort[0]=field1&sort[1]=field2
?sort[0]=field1&sort[1]=field2,sort_type
?sort[0]=field1,sort_type&sort[1]=field2,sort_type
In User.php
protected $filters = ['sort'];Single sort:
https://example.com?sort=created_at
Output:
| name | username | age | created_at | |
|---|---|---|---|---|
| mehrad | mehrad@example.com | mehrad123 | 20 | 2020-09-01 |
| reza | reza@example.com | reza123 | 20 | 2020-10-01 |
| hossein | hossein@example.com | hossein123 | 22 | 2020-11-01 |
| dariush | dariush@example.com | dariush123 | 22 | 2020-12-01 |
- Note that when you're not defining
sort_type, It'll beascby default.
Multiple sorts:
https://example.com?sort[0]=age,desc&sort[1]=created_at,desc
Output:
| name | username | age | created_at | |
|---|---|---|---|---|
| dariush | dariush@example.com | dariush123 | 22 | 2020-12-01 |
| hossein | hossein@example.com | hossein123 | 22 | 2020-11-01 |
| reza | reza@example.com | reza123 | 20 | 2020-10-01 |
| mehrad | mehrad@example.com | mehrad123 | 20 | 2020-09-01 |
Bare in mind that sort parameter with invalid values will be ignored from query and has no effect to the result.
Comparisons are consist of 6 filters:
- greater
- greater_or_equal
- less
- less_or_equal
- between
- not_between
Conventions:
?greater=field,value
?greater_or_equal=field,value
?less=field,value
?less_or_equal=field,value
?between=field,value1,value2
?not_between=field,value1,value2
In User.php
protected $filters = [
'greater',
'greater_or_equal',
'less',
'less_or_equal',
'between',
'not_between'
];Example of greater:
https://example.com?greater=age,20
Output:
| name | username | age | created_at | |
|---|---|---|---|---|
| hossein | hossein@example.com | hossein123 | 22 | 2020-11-01 |
| dariush | dariush@example.com | dariush123 | 22 | 2020-12-01 |
Example of not_between:
https://example.com?not_between=age,21,30
Output:
| name | username | age | created_at | |
|---|---|---|---|---|
| mehrad | mehrad@example.com | mehrad123 | 20 | 2020-09-01 |
| reza | reza@example.com | reza123 | 20 | 2020-10-01 |
Bare in mind that comparison parameters with invalid values will be ignored from query and has no effect to the result.
In clause is the equivalent to where in sql statement.
Convention:
?in=field,value1,value2
In User.php
protected $filters = ['in'];Example:
https://example.com?in=name,mehrad,reza
Output:
| name | username | age | created_at | |
|---|---|---|---|---|
| mehrad | mehrad@example.com | mehrad123 | 20 | 2020-09-01 |
| reza | reza@example.com | reza123 | 20 | 2020-10-01 |
Bare in mind that in parameter with invalid values will be ignored from query and has no effect to the result.
Like clause is the equivalent to like '%value%' sql statement.
Conventions:
?like=field,value
?like[0]=field1,value1&like[1]=field2,value2
In User.php
protected $filters = ['like'];Single like:
https://example.com?like=name,meh
Output:
| name | username | age | created_at | |
|---|---|---|---|---|
| mehrad | mehrad@example.com | mehrad123 | 20 | 2020-09-01 |
Multiple likes:
https://example.com?like[0]=name,meh&like[1]=username,dar
Output:
| name | username | age | created_at | |
|---|---|---|---|---|
| mehrad | mehrad@example.com | mehrad123 | 20 | 2020-09-01 |
| dariush | dariush@example.com | dariush123 | 22 | 2020-12-01 |
Bare in mind that like parameter with invalid values will be ignored from query and has no effect to the result.
Generally when your query string parameters are not one of previous available methods, It'll get filtered by the default filter which is the where sql statement. It's the proper filter when you need to directly filter one of your table's columns.
Conventions:
?field=value
?field1=value&field2=value
?field1[0]=value1&field1[1]=value2
?field1[0]=value1&field1[1]=value2&field2[0]=value1&field2[1]=value2
Assuming we want to filter name, username and age database columns, In User.php
protected $filters = ['name', 'username', 'age'];Example:
https://example.com?name=mehrad
Output:
| name | username | age | created_at | |
|---|---|---|---|---|
| mehrad | mehrad@example.com | mehrad123 | 20 | 2020-09-01 |
Example:
https://example.com?age=22&username=dariush123
Output:
| name | username | age | created_at | |
|---|---|---|---|---|
| dariush | dariush@example.com | dariush123 | 22 | 2020-12-01 |
Example:
https://example.com?name[0]=mehrad&name[1]=dariush
Output:
| name | username | age | created_at | |
|---|---|---|---|---|
| mehrad | mehrad@example.com | mehrad123 | 20 | 2020-09-01 |
| dariush | dariush@example.com | dariush123 | 22 | 2020-12-01 |
Example:
https://example.com?name[0]=mehrad&name[1]=dariush&username[0]=mehrad123&username[1]=reza1234
Output:
| name | username | age | created_at | |
|---|---|---|---|---|
| mehrad | mehrad@example.com | mehrad123 | 20 | 2020-09-01 |
Bare in mind that default filter parameter with invalid values will be ignored from query and has no effect to the result.
By custom filters you can define your own methods as filters. This helps with the Open/Closed of SOLID principles, Hence each time a new filter is needed, you don't have to edit previous filters and you can just write a separate method for it.
Let's create a custom filter. Assuming you want to create a filter named all_except which retrieves all users except the one that is specified:
In User.php
protected $filters = ['all_except'];
public function all_except($query, $value) {
return $query->where('name', '!=', $value);
}To test our newly added filter:
https://example.com?all_except=mehrad
Output:
| name | username | age | created_at | |
|---|---|---|---|---|
| reza | reza@example.com | reza123 | 20 | 2020-10-01 |
| hossein | hossein@example.com | hossein123 | 22 | 2020-11-01 |
| dariush | dariush@example.com | dariush123 | 22 | 2020-12-01 |
Note that your custom defined filters have the most priority which means you can even override available filters.
For example lets change in filter in a way that only accepts 3 values:
In User.php
protected $filters = ['in'];
public function in($query, $value) {
$exploded = explode(',', $value);
if(count($exploded) != 4) {
// throwing an exception or whatever you like to do
}
$field = array_shift($exploded);
return $query->whereIn($field, $exploded);
}Another good example for custom filters are when you don't want to expose your database table's column name. For example assume we don't want to expose that we have a column named username in users table:
In User.php
protected $filters = ['by'];
public function by($query, $value) {
return $query->where('username', $value);
}https://example.com?by=dariush123
Output:
| name | username | age | created_at | |
|---|---|---|---|---|
| dariush | dariush@example.com | dariush123 | 22 | 2020-12-01 |
In order to prevent your model to get messy or populated with filter methods, You can create a trait for it and put everything about filters inside the trait.
The $filters property in your model is acting kind of global for that model. It means when you use filter() method on your eloquent query, it'll always performs all the $filters filters.
There might be situations that based on a condition you need to specify which filters exactly you wish to be filtered.
To achieve this you can specify your desired filters as arguments in filter() method.
Example:
In your query:
User::filter('in')->get();in=name,mehrad,reza&like=name,mehrad
Output:
| name | username | age | created_at | |
|---|---|---|---|---|
| mehrad | mehrad@example.com | mehrad123 | 20 | 2020-09-01 |
| reza | reza@example.com | reza123 | 20 | 2020-10-01 |
If the in argument was not specified, The result of query would be only one record (mehrad).
Another example:
In your query:
User::filter('like', 'name')->get();like=name,mehrad,reza,dariush,hossein&name[0]=mehrad&name[1]=hossein&username=mehrad
Output:
| name | username | age | created_at | |
|---|---|---|---|---|
| mehrad | mehrad@example.com | mehrad123 | 20 | 2020-09-01 |
| hossein | hossein@example.com | hossein123 | 22 | 2020-11-01 |
If you find this package useful and you want to encourage me to maintain and work on it, Just press the star button to declare your willing.
Send me as much as a cup of tea worth in your country, so I'll have the energy to maintain this package.
- Ethereum: 0x2D5BFdEc132F9F0E9498Fb0B58C800db4007D154