leonardjke/laravel-query-filter

Laravel package for dynamically filtering Eloquent results using URL query string.

dev-master 2020-04-26 11:00 UTC

Requires

• php: >=7.2.5
• illuminate/support: >=4.1

MIT

• leonardjke

README

Based on heroicpixels/filterable (https://github.com/heroicpixels/filterable) This package gives you a convenient way to automatically filter Eloquent results based on query string parameters in the URL. Filterable parses the query string, compares it with columns that you'd like to automatically filter, then creates a dynamic scope that is used by Eloquent to construct the SQL.

• Installation
• Copyright & License
• Usage
  • Single Value
  • Multiple Values
  • Multiple Parameters
  • Boolean Operators
  • Comparison Operators

Installation

Add the package to 'require' in your composer.json file:

"require": {
    "leonardjke/laravel-query-filter": "dev-master"
},

Run 'composer dump-autoload' from the command line:

#composer dump-autoload

License

Copyright 2020 leonardjke

Released under MIT license (http://opensource.org/licenses/MIT). See LICENSE file for details.

Usage

Filterable.php

class Object extends Eloquent {

   use Leonardjke\LaravelQueryFilter\Filterable;

}

The examples below use the Filterable class!

In the above example, class Object corresponds to table 'objects':

id	color	shape	total
1	red	square	150
2	blue	square	2000
3	green	circle	575
4	yellow	triangle	15
5	red	triangle	900
6	red	triangle	600

Filterable Columns

Specify the column you want to automatically filter.

$columns = [ 'color', 'shape', 'total' ];

For example:

 http://www.your-site/?color=blue&shape=round&total=500

You can also alias the columns if you prefer not to reveal them:

$columns = [ 'col' => 'color', 'sha' => 'shape', 'tot' => 'total' ];

For example:

http://www.your-site/?col=blue&sha=round&tot=500

To filter results, simply pass the columns to Eloquent using filter():

$objects = Object::filter($requers)->get()->toArray();

The following examples demonstrate how query string parameters can be used.

Single Value

?color=red

SELECT ... WHERE ... color = 'red'

id	color	shape	total
1	red	square	150
5	red	triangle	900
6	red	triangle	600

Multiple Values

?color[]=red&color[]=blue

SELECT ... WHERE ... color = 'red' OR color = 'blue'

id	color	shape	total
1	red	square	150
2	blue	square	2000
5	red	triangle	900
6	red	triangle	600

Multiple Parameters

?color[]=red&shape[]=triangle

SELECT ... WHERE ... color = 'red' AND shape = 'triangle'

id	color	shape	total
5	red	triangle	900
6	red	triangle	600

Boolean Operators

?color[]=red&shape[]=triangle&bool[shape]=or

SELECT ... WHERE ... color = 'red' OR shape = 'triangle'

id	color	shape	total
4	yellow	triangle	15
5	red	triangle	900
6	red	triangle	600

Comparison Operators

Greater Than

?total=599&operator[total]=>

SELECT ... WHERE ... total > '599'

id	color	shape	total
2	blue	square	2000
5	red	triangle	900
6	red	triangle	600

Less Than

?total=600&operator[total]=<

SELECT ... WHERE ... total < '600'

id	color	shape	total
1	red	square	150
3	green	circle	575
4	yellow	triangle	15

Not Equal

?shape=triangle&operator[shape]=!=

SELECT ... WHERE ... shape != 'triangle'

id	color	shape	total
4	yellow	triangle	15
5	red	triangle	900
6	red	triangle	600

Between (TODO)

?total[start]=900&total[end]=5000

SELECT ... WHERE ... total BETWEEN '900' AND '5000'

id	color	shape	total
2	blue	square	2000
5	red	triangle	900