Detective
Eloquent extension for advanced filtering. Designed to be used with URL parameters in API context., (*1)
Installation
composer require lucasbedout/detective
Configuration
Just replace Eloquent by the detective model in your model files., (*2)
use \Detective\Model as Detective;
class YourModel extends Detective
{
// Classic eloquent class
}
If you need relationship filtering, just list your relationship methods like this., (*3)
use \Detective\Model as Detective;
class YourModel extends Detective
{
public static $relationships = ['relation1', 'relation2'];
public function relation1()
{
return $this->belongsTo('Relation1');
}
public function relation2()
{
return $this->belongsToMany('Relation2');
}
}
Usage example
You can use the test laravel server provided with the package to test it, you have products, categories and stores table with configured relationships., (*4)
You can see the list of all valid syntaxes at the bottom of this file., (*5)
Add the corresponding route, (*6)
Route::get('/products', function() {
$products = Product::filter(Input::all())->get();
return response()->json($products);
});
Then just query it, (*7)
/GET /products?name=iPhone*
Will return a list of products with their name starting with iPhone (caution, the search is case sensitive)., (*8)
You can also query a relationship, (*9)
/GET /products?category-name=Laptop
And even a pivot value, (*10)
/GET /products/?category-name=Laptop&stores-pivot-quantity=>5
This query will return all products in the Laptop category with at least one store where the quantity of products is > 5, (*11)
You can also use the filter method with a custom array, (*12)
$array = ['name' => 'Home*', 'created_at' => '<2015-06-11'];
$products = Product::filter($array)->get(); // Products in the Home% categories, created before the 11/06/2015.
The filter method returns a query builder, so you can chain any builder method you need., (*13)
$products = Product::filter($data)->groupBy('category_id')->paginate(10);
The Detective\Model class create a class context when you call the Detective::filter() method, it retrieves all fields and types from the database, then check your $relationships array and if there is one, it retrieves also your relationships informations (keys, type, ...)., (*14)
The array passed to Detective::filter() is parsed, the Detective\Filters\Builder class make a correspondance between your array and the table informations, then the query is builded., (*15)
The Detective\Database\Context class has a lot of useful public methods, just check the code !, (*16)
Valid filter syntaxes
3 atomic types
- number
'int', 'double', 'float', 'decimal', 'tinyint'
- string
'varchar', 'text', 'blob', 'longtext'
- date
'date', 'datetime', 'timestamp', 'time'
Filtering parameter name
You can use multiple syntaxe, each one corresponding to a model attribute or relation., (*17)
-
property : filter by object property (ex : name=yourname)
-
relation : filter by relation primary key (ex : category=1)
-
relation-property : filter by relation property (ex : category-name=Laptop)
-
relation-property-last : filter by relation property of the last relation object(ex : category-name-last=Laptop), also works with first
-
relation-pviot-property: filter by relation pivot property (ex : stores-pivot-quantity=10)
Once you've named your parameter, you can provide the following values (all syntaxes work for relations or fields), (*18)
Number, (*19)
-
id=1 : get model with id=1
-
id=1,2 : get model with id=1 OR id=2
-
id=10,<5 : get model with id=10 OR id<5
-
id=5-10 : get model with id between 5 and 10 (SQL BETWEEN operator)
You can combine, (*20)
-
id=>5,1-10,<12,5 is a valid syntax
String, (*21)
-
name=toto : get model with name LIKE toto
-
name=toto* : get model with name LIKE 'toto%'
-
name=toto*,*ta*ta* : get model with name LIKE 'toto%' OR name LIKE '%ta%ta%'
Date, (*22)
-
date=2015-03-03 : get model with date LIKE 2015-03-03
-
date=2015-03-03* : get model with date LIKE 2015-03-03%
-
date=2015-03-03*,>2015-05-05 : get model with date LIKE 2015-03-03% OR date > 2015-05-05
Order
You can also provide an orderby parameter., (*23)
- orderby=name : order your results by name
- orderby=name,created_at : order your results by name and creation date
- direction=asc / direction=desc : direction of your orderby parameters (will move in the future)
Development Roadmap
- Add existence checking (not null, not empty)
- Add not filter
- Performance issues with big Many To Many queries (>100k rows)
Wallogit.com
