Laravel Query Filter

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 |