A useful library to make filters for Eloquent.
This library is useful to create search filters in your Rest API using the Eloquent.
🇧🇷🚀🚀🚀
The Eloquent Filter library can be used to create patterns for search criterias on models in your Laravel Project. The idea is aggregate filters simply passing the values in your request payload.
For instalation, you should be use Composer. Run the follow command:
composer require laravellegends/eloquent-filter
The LaravelLegends\EloquentFilter\Concerns\HasFilter
trait can be used in models that will be apply the search filters.
use LaravelLegends\EloquentFilter\Concerns\HasFilter;
class User extends Model
{
use HasFilter;
}
The HasFilter
trait provides the filter
and withFilter
methods.
A simple way to use this library in your Laravel application is calling the filter
method before get results of your model.
Example:
class UsersController extends Controller
{
use App\Models\User;
public function index()
{
return User::filter()->paginate();
}
// or
public function index()
{
return User::latest('id')->filter()->paginate();
}
// or
public function index(Request $request)
{
return User::filter($request)->paginate();
}
}
You can show the results when call /api/users?exact[id]=1
. The sql query "select * from users where (id = 1)"
will be applied.
Note: Show the rules session to more information.
Another way, is using the specific filter for a model. You can inherit the ModelFilter class to create a custom filter for a model.
For create this class, you should be use the command php artisan make:filter
, as follow example:
$ php artisan make:filter UserFilter
The above command will be generate the follow class:
namespace App\Filters;
use LaravelLegends\EloquentFilter\Filters\ModelFilter;
class UserFilter extends ModelFilter
{
public function getFilterables(): array
{
return [
'role_id' => 'not_equal', // or ['not_equal']
'name' => ['contains', 'starts_with'],
];
}
}
In Controller
use App\Models\User;
use Illuminate\Http\Request;
use LaravelLegends\EloquentFilter\Filter;
class UsersController extends Controller
{
// api/users?starts_with[name]=Wallace¬_equal[role_id]=2
public function index(Request $request)
{
return User::withFilter(new UserFilter, $request)
->orderBy('name')
->get();
}
}
The above code internally will be called as follow example:
User::where(function ($query) {
$query->where('name', 'LIKE', 'Wallace%');
$query->where('role_id', '<>', '2');
})
->orderBy('name')
->get();
This library internally apply filters based on query string parameters with special keyworks names.
See all paramaters follow:
The maximum value of a column. The url api/users?max[field]=100
is like a User::where('field', '<=', 100)
.
The minimum value of a column. The url api/users?min[age]=33
is like a User::where('age', '>=', 33)
.
A search term contained in a column.
The url api/users?contains[name]=wallace
is like a User::where('name', 'LIKE', '%wallace%')
.
Search a value according to end content of string. Is similar to a LIKE
with %$value
value.
Filter the field when the value starts with a certain value.
A url api/users?starts_with[name]=brcontainer
Sounds like a User::where('name', 'LIKE', 'brcontainer%')
.
Search by a exact value of the field·
A url api/users?exact[email][email protected]
Sounds like a User::where('name', '=', '[email protected]')
.
Filter by relationship. You can use the 0
or 1
value.
Example:
The url api/users?has[posts]=1
is like a User::has('posts')
The url api/users?has[posts]=0
is like a User::doesntHave('posts')
Apply WHERE IS NULL
or WHERE IS NOT NULL
to a query.
Example:
The url api/users?is_null[cpf]=1
is like a User::whereNull('cpf')
The url api/users?is_null[age]=0
is like a User::whereNotNull('age')
Searchs when a column NOT HAS the passed values.
Example:
A url api/users?not_in[role][]=1¬_in[role][]=2
é equivalente à User::whereNotIn('role', [1, 2])
Note: When the not_in[my_field]
is a empty array, no action will be taken.
Searchs when a column HAS the passed values.
Example:
The url api/users?in[role][]=10&in[role][]=20
sounds like a User::whereIn('role', [10, 20])
NOTE: When the in[my_field]
is a empty array, no action will be taken.
Search by a maximium value of a date field.
A url api/users?date_max[created_at]=2021-01-01
sounds like a User::whereDate('created_at', '<=', '2021-01-01')
Search by a minimun value of a date field.
Example:
A url api/users?date_min[created_at]=2021-01-01
sounds like a User::whereDate('created_at', '>=', '2021-01-01')
Search by not equal value passed. If you use in related field, the whereDoesntHave will be applied applied.
Example:
The url api/users?not_equal[profile_id]=3
sounds like a
User::where('profile_id', '<>', '3');
The url api/users?not_equal[roles.id]=1
sounds like a
User::whereDoesntHave('roles', fn ($query) => $query->where('id', '=', 3));
The url api/users?year_max[created_at]=2000
sounds like a
User::whereYear('created_at', '<=', 2000);
The url api/users?year_min[created_at]=1998
sounds like a
User::whereYear('created_at', '>=', 1998);
The url api/users?year_exact[created_at]=1998
sounds like a
User::whereYear('created_at', '=', 1998);
You can apply the search filters in the relatioship methods defined in your model.
For example:
Model:
class User extends Model
{
use HasFilter;
public function phones()
{
return $this->hasMany(Phone::class, 'user_id');
}
}
Filters:
class UserFilter extends ModelFilter
{
public function getFilterables(): array
{
return [
'id' => ['exact', 'not_equal'],
'created_at' => ['year_exact', 'date_max', 'date_min'],
'phones.number' => ['contains'],
// or
'phones' => new PhoneFilter,
];
}
}
class PhoneFilter extends ModelFilter
{
public function getFilterables(): array
{
return [
'number' => 'contains'
];
}
}
class UserController extends Controller
{
public function index()
{
// api/users?not_in[role_id][]=1¬_in[role_id][]=3
// select * from users where (role_id NOT IN (1, 3))
return User::withFilter(new UserFilter)->paginate();
}
// Or, apply filter as nested query
public function index()
{
// api/users?exact[role_id]=1
// select * from users where (role_id = 1)
return User::where(UserFilter::toClosure())->paginate();
}
// Or apply in your query as base condition
public function index()
{
// api/users?exact[role_id]=1
// select * from users where role_id = 1
return User::tap(UserFilter::toClosure())->paginate();
}
}
In the following example, the user will be filtered for the related phone containing the value 55
.
The api/users?exact[phones.number]=55
is like to:
User::where(function ($query) {
$query->whereHas('phones', function ($query) {
$query->where('number', '=', '55');
});
})->paginate();
If you use axios
library, you can use the params
options to include the above filters.
Example:
const api = axios.create({
baseURL: 'http://localhost:8000/api'
});
api.get('users', {
params: {
'in[role]' : [1, 2, 3],
'contains[name]' : 'Maxters',
'is_null[name]' : 0
}
})