Ever been stuck with filtering a database table with lots of GET parameters? I can only imagine how bulky your code will be. Relax, the easiest solution to this problem is right here. Let's say we have to query our records table with the following get parameters
You can install the package via composer:
composer require infinitypaul/laravel-database-filterThe package will automatically register itself, but if your laravel versions is < 5.5 you will need to add Infinitypaul\LaravelDatabaseFilter\LaravelDatabaseFilterServiceProvider::class, service provider under your config/app.php file.
Once the package is installed, an artisan command is available to you.
php artisan make:filter We would be working with the records table below:
| Id | Department | Level | Score | Grade |
|---|---|---|---|---|
| 1 | csc | 200 | 68 | b |
| 2 | physics | 100 | 90 | a |
| 3 | csc | 100 | 90 | a |
| 4 | physics | 200 | 60 | b |
| 5 | csc | 200 | 80 | a |
The Filtering be done in 6 simple steps. Perfect right! Let's begin:
Create a mother filter class, this is where all filters will be recorded. This can be created with this one line of code:
php artisan make:filter RecordFilter --modelThis package will generate a new PHP file RecordFilter.php under app/Filters folder. This is where all other filters will be created.. It wil look like this
<?php
namespace App\Filters;
use Infinitypaul\LaravelDatabaseFilter\Abstracts\FiltersAbstract;
class RecordFilter extends FiltersAbstract {
protected $filters = [];
}Since we are working with the records table, we will be working on the Record model. So open your Record model and include the following: * The Filter Trait * The Record Filter Class
namespace App;
use App\Filters\RecordFilter;
use Infinitypaul\LaravelDatabaseFilter\Traits\filterTrait;
class Course extends Model
{
use filterTrait;
protected $filter = RecordFilter::class; //mother filter class
}We would be filtering with 3 parameters (dept, level, grade):
Let's create filter classes for each filter
php artisan make:filter DeptFilter
php artisan make:filter LevelFilter
php artisan make:filter GradeFilterThe above commands will also generate a new PHP file under app/Filters folder with the name DebtFilter.php, LevelFilter.php and GradeFilter.php which will look like below.
namespace App\Filters;
use Illuminate\Database\Eloquent\Builder;
use Infinitypaul\LaravelDatabaseFilter\Abstracts\FilterAbstract;
class DebtFilter extends FilterAbstract
{
public function mappings ()
{
return [];
}
public function filter(Builder $builder, $value)
{
return $builder;
}
}
Well Done!!! Let's move to the next step
Now we write our logic in each class:
In our DeptFilter class, we write the following:
namespace App\Filters;
use Illuminate\Database\Eloquent\Builder;
use Infinitypaul\LaravelDatabaseFilter\Abstracts\FilterAbstract;
class DeptFilter extends FilterAbstract
{
public function mappings ()
{
return [];
}
public function filter(Builder $builder, $value)
{
return $builder->where('dept', $value);
}
}In our LevelFilter class, we write the following:
namespace App\Filters;
use Illuminate\Database\Eloquent\Builder;
use Infinitypaul\LaravelDatabaseFilter\Abstracts\FilterAbstract;
class LevelFilter extends FilterAbstract
{
public function mappings ()
{
return [];
}
public function filter(Builder $builder, $value)
{
return $builder->where('level', $value);
}
}In our GradeFilter class, we write the following:
namespace App\Filters;
use Illuminate\Database\Eloquent\Builder;
use Infinitypaul\LaravelDatabaseFilter\Abstracts\FilterAbstract;
class GradeFilter extends FilterAbstract
{
public function mappings ()
{
return [];
}
public function filter(Builder $builder, $value)
{
return $builder->where('grade', $value);
}
}The filter method takes in our conditions or whatever checks against the database
Bravo on completing that. Moving on!!!
We write our search parameters against the filter class. Let's open up our mother filter class - RecordFilter to register all the conditions we have generated.
namespace App\Filters;
use Infinitypaul\LaravelDatabaseFilter\Abstracts\FiltersAbstract;
class RecordFilter extends FiltersAbstract {
protected $filters = [
'dept' => DeptFilter::class,
'level' => LevelFilter::class,
'grade' => GradeFilter::class
];
}The $Filter array key is will be the query params.
Assuming we have a controller RecordController, we can create a function to get our record:
namespace App\Http\Controllers;
use App\Course;
use App\Filters\AccessFilter;
use Illuminate\Http\Request;
class RecordController extends Controller
{
public function index(Request $request){
return Course::filter($request)->get();
}
}We are all done!!!
When we enter the following into our browser
We will get the following result:
{
"data": [
{
"id": 5,
"department": "csc",
"level": 200,
"score": "80",
"grade": "a"
}
]
}
To be strict about query params input, we can use the mapping method or leave empty for free entry.
Let's say when returning the data, we want all department with csc to return as CSC. We return values to the mappings function in the DeptFilter class as shown below:
namespace App\Filters;
use Illuminate\Database\Eloquent\Builder;
use Infinitypaul\LaravelDatabaseFilter\Abstracts\FilterAbstract;
class DeptFilter extends FilterAbstract
{
public function mappings ()
{
return [
'csc' => 'CSC'
];
}
public function filter(Builder $builder, $value)
{
return $builder->where('dept', $value);
}With the above setup, once csc is entered in your query params, it will return CSC as value.
Lastly, You can add local scoped filter() by passing an array of filter into the filter scope.
public function index(Request $request){
return Record::filter($request, ['score' => DifficultyFilter::class])->get();
}You can also override your filter property from your controller in this manner
public function course(Request $request){
return Record::filter($request, PaulFilter::class, ['score' => DifficultyFilter::class])->get();
}So instead of your filter to use the RecordFilter it will make use of PaulFilter
If you have spotted any bugs, or would like to request additional features from the library, please file an issue via the Issue Tracker on the project's Github page: https://github.com/infinitypaul/laravel-database-filter/issues.
Please see CONTRIBUTING for details.
If you discover any security related issues, please email [email protected] instead of using the issue tracker.
Why not star the github repo? I'd love the attention! Why not share the link for this repository on Twitter or HackerNews? Spread the word!
Don't forget to follow me on twitter!
Thanks! Edward Paul.
The MIT License (MIT). Please see License File for more information.