Laravel Eloquent Filter
A laravel package to filter eloquent models and their relationships based on URL query strings.
Introduction
Suppose we have following two models,
User Model
<?php
namespace App\Models;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Notifications\Notifiable;
use Illuminate\Auth\Authenticatable;
use Illuminate\Auth\Passwords\CanResetPassword;
use Illuminate\Foundation\Auth\Access\Authorizable;
use Illuminate\Contracts\Auth\Authenticatable as AuthenticatableContract;
use Illuminate\Contracts\Auth\Access\Authorizable as AuthorizableContract;
class User extends Model implements
AuthenticatableContract,
AuthorizableContract
{
use Authenticatable, Authorizable, CanResetPassword;
use Notifiable;
}Post Model
<?php
namespace App\Models;
use Illuminate\Database\Eloquent\Model;
class Post extends Model
{
/**
* Get the publisher for this model.
*/
public function publisher()
{
return $this->belongsTo(User::class, 'user_id', 'id');
}
}And now we want to return a list of posts filtered by multiple parameters through URL query string. When we navigate to:
/posts?score=4&view_count='10-100'&publisher['age']='20-30'&publisher['type']='guest'&created_at='02/02/2018-04/02/2018'
In controller if we dump $request->all(), we will see following parameters:
[
score => '4',
view_count => '10-100',
publisher => [
age => '20-30',
type => 'guest'
],
created_at => '02/02/2018-04/02/2018'
]To filter the post model by all those parameters we would need to do something like:
<?php
namespace App\Http\Controllers;
use Illuminate\Http\Request;
use App\Http\Requests;
use App\Post;
class PostController extends Controller
{
public function index(Request $request)
{
$query = Post::where('score', $request->input('score'));
if ($request->has('view_count'))
$query->whereBetween('view_count', explode('-',$request->get('view_count')));
if ($request->has('created_at'))
$query->whereBetween('created_at', explode('-',$request->get('created_at')));
// filter relation
if ($request->has('publisher')){
$query->whereHas('publisher', function ($q) use ($request)
{
$publisher = $request->get('publisher');
if(!empty($publisher['age']))
$q->whereBetween('age', explode('-',$publisher['age']));
if(!empty($publisher['type']))
$q->where('type', $publisher['type']);
return $q;
});
}
return $query->get();
}
}You can filter same model (along with it's relationships) by using eloquent filter:
<?php
namespace App\Http\Controllers;
use Illuminate\Http\Request;
use App\Http\Requests;
use App\Post;
class PostController extends Controller
{
public function index(Request $request)
{
return Post::filter()->get();
}
}Installation
Install the package via composer
foo@bar:~$ composer require velitsol/eloquent-filterUsage
First you need to add Filterable trait to your model as the following:
use VelitSol\EloquentFilter\Filtrable;
class Post extends Model {
use Filtrable;
}Call filter method just before get()
<?php
namespace App\Http\Controllers;
use Illuminate\Http\Request;
use App\Http\Requests;
use App\Post;
class PostController extends Controller
{
public function index(Request $request)
{
return Post::filter()->get();
}
}Supported Filters
Equality
/posts?field_name=value
Range
/posts?field_name='start-end'
Relationships
/posts?relationship_name[field_name]=value
Or range
/posts?relationship_name[field_name]='start-end'
Custom
Sometimes we need to filter data on some status which depends on some other field. In below example, we want to filter all paid items, we don't have is_paid boolean field in table, but we do have paid_at (date) field which is set when an item is paid. So we can filter on the basis of it's value. For custom filter to work, we need to place _ at the end of field name.
<div>
<label for="paid_at">Is Paid</label>
<select name="paid_at_" id="paid_at">
<option value="is-not-null">Yes</option>
<option value="is-null">No</option>
</select>
</div>So query string should look like
/posts?field_name_='is-null'
This package also supports filtering of appended attributes.
TODO
- Support for lt, gt, lte, gte, neq query strings
License
This open-source software is licensed under the MIT license.
