How To Filter Your Eloquent Queries Based on URL Query String in Laravel Like a Pro
Tags: laravel, eloquent, query, filter
Published: 2020-07-31 17:50:00

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/x4fufgo280rufwu551p3.png)

## Describing the Problem

You have probably faced the situation where you needed to filter your query based on given parameters in url query-string and after developing the logics, You've had a code-base like what we can in the above image.

This approach works, But it's not a good practice.

When the number of parameters starts to grow, The number of these kind of `if` statements also grows and your code gets huge and hard to maintain.
 
Also it's against the Open/Closed principal of SOLID principles, Because when you have a new parameter, You need to get into your existing code and add a new logic (which may breaks the existing implementations).

So we have to design a way to make our filters logics separated from each other and apply them into the final query, which is the whole idea behind the package I wrote for Laravel.

### Laravel Filter Query String

- First you need to install the package:

`$ composer require mehradsadeghi/laravel-filter-querystring`

- Then you should `use` the `FilterQueryString` trait in your model, And define `$filters` property which can be consist of available filters or your custom filters.

```php
use Mehradsadeghi\FilterQueryString\FilterQueryString;

class User extends Model
{
    use FilterQueryString;

    protected $filters = [];

    ...
}
```
- Then you need to use `filter()` method in your eloquent query. For example:

```php
User::select('name')->filter()->get();
```

### Available Methods
- Sort
- Comparisons
- In
- Like
- Where clause

For the purpose of explaining each method, Imagine we have such data in our `users` table:

| id  |   name   |           email            |  username  |  age | created_at 
|:---:|:--------:|:--------------------------:|:----------:|:----:|:----------:|
|  1  | mehrad   | mehrad<i></i>@example.com  | mehrad123  |  20  | 2020-09-01 |
|  2  | reza     | reza<i></i>@example.com    | reza123    |  20  | 2020-10-01 |
|  3  | hossein  | hossein<i></i>@example.com | hossein123 |  22  | 2020-11-01 |
|  4  | dariush  | dariush<i></i>@example.com | dariush123 |  22  | 2020-12-01 |

And assume our query is something like this:

```php
User::filter()->get();
```

### Sort
Sort is the equivalent to `order by` sql statement which can be used flexible in `FilterQueryString`:

Conventions:

```
?sort=field
?sort=field,sort_type
?sort[0]=field1&sort[1]=field2
?sort[0]=field1&sort[1]=field2,sort_type
?sort[0]=field1,sort_type&sort[1]=field2,sort_type
```

In User.php
```php
protected $filters = ['sort'];
```
**Single `sort`**:

`https://example.com?sort=created_at`

Output:

|   name   |           email            |  username  |  age | created_at 
|:--------:|:--------------------------:|:----------:|:----:|:----------:|
| mehrad   | mehrad<i></i>@example.com  | mehrad123  |  20  | 2020-09-01 |
| reza     | reza<i></i>@example.com    | reza123    |  20  | 2020-10-01 |
| hossein  | hossein<i></i>@example.com | hossein123 |  22  | 2020-11-01 |
| dariush  | dariush<i></i>@example.com | dariush123 |  22  | 2020-12-01 |

- **Note** that when you're not defining `sort_type`, It'll be `asc` by default.

**Multiple `sort`s**:

`https://example.com?sort[0]=age,desc&sort[1]=created_at,desc`

Output:

|   name   |           email            |  username  |  age | created_at 
|:--------:|:--------------------------:|:----------:|:----:|:----------:|
| dariush  | dariush<i></i>@example.com | dariush123 |  22  | 2020-12-01 |
| hossein  | hossein<i></i>@example.com | hossein123 |  22  | 2020-11-01 |
| reza     | reza<i></i>@example.com    | reza123    |  20  | 2020-10-01 |
| mehrad   | mehrad<i></i>@example.com  | mehrad123  |  20  | 2020-09-01 |


### Comparisons
Comparisons are consist of 6 filters:
- greater
- greater_or_equal
- less
- less_or_equal
- between
- not_between

Conventions:

```
?greater=field,value
?greater_or_equal=field,value
?less=field,value
?less_or_equal=field,value
?between=field,value1,value2
?not_between=field,value1,value2
```

In User.php
```php
protected $filters = [
    'greater',
    'greater_or_equal',
    'less',
    'less_or_equal',
    'between',
    'not_between'
];
```

**Example of `greater`**:

`https://example.com?greater=age,20`

Output:

|   name   |           email            |  username  |  age | created_at 
|:--------:|:--------------------------:|:----------:|:----:|:----------:|
| hossein  | hossein<i></i>@example.com | hossein123 |  22  | 2020-11-01 |
| dariush  | dariush<i></i>@example.com | dariush123 |  22  | 2020-12-01 |

**Example of `not_between`**:

`https://example.com?not_between=age,21,30`

Output:

|   name   |           email            |  username  |  age | created_at 
|:--------:|:--------------------------:|:----------:|:----:|:----------:|
| mehrad   | mehrad<i></i>@example.com  | mehrad123  |  20  | 2020-09-01 |
| reza     | reza<i></i>@example.com    | reza123    |  20  | 2020-10-01 |

### In
In clause is the equivalent to `where in` sql statement.

Convention:

```
?in=field,value1,value2
```

In User.php
```php
protected $filters = ['in'];
```
**Example**:

`https://example.com?in=name,mehrad,reza`

Output:

|   name   |           email            |  username  |  age | created_at 
|:--------:|:--------------------------:|:----------:|:----:|:----------:|
| mehrad   | mehrad<i></i>@example.com  | mehrad123  |  20  | 2020-09-01 |
| reza     | reza<i></i>@example.com    | reza123    |  20  | 2020-10-01 |

### Like
Like clause is the equivalent to `like '%value%'` sql statement.

Conventions:

```
?like=field,value
?like[0]=field1,value1&like[1]=field2,value2
```

In User.php
```php
protected $filters = ['like'];
```
**Single `like`**:

`https://example.com?like=name,meh`

Output:

|   name   |           email            |  username  |  age | created_at 
|:--------:|:--------------------------:|:----------:|:----:|:----------:|
| mehrad   | mehrad<i></i>@example.com  | mehrad123  |  20  | 2020-09-01 |


**Multiple `like`s**:

`https://example.com?like[0]=name,meh&like[1]=username,dar`

Output:

|   name   |           email            |  username  |  age | created_at 
|:--------:|:--------------------------:|:----------:|:----:|:----------:|
| mehrad   | mehrad<i></i>@example.com  | mehrad123  |  20  | 2020-09-01 |
| dariush  | dariush<i></i>@example.com | dariush123 |  22  | 2020-12-01 |

### Where Clause (default filter)
Generally when your query string parameters are not one of previous available methods, It'll get filtered by the default filter which is the `where` sql statement. It's the proper filter when you need to directly filter one of your table's columns.

Conventions:

```
?field=value
?field1=value&field2=value
?field1[0]=value1&field1[1]=value2
?field1[0]=value1&field1[1]=value2&field2[0]=value1&field2[1]=value2 
```

Assuming we want to filter `name`, `username` and `age` database columns, In User.php 
```php
protected $filters = ['name', 'username', 'age'];
```
**Example**:

`https://example.com?name=mehrad`

Output:

|   name   |           email            |  username  |  age | created_at 
|:--------:|:--------------------------:|:----------:|:----:|:----------:|
| mehrad   | mehrad<i></i>@example.com  | mehrad123  |  20  | 2020-09-01 |


**Example**:

`https://example.com?age=22&username=dariush123`

Output:

|   name   |           email            |  username  |  age | created_at 
|:--------:|:--------------------------:|:----------:|:----:|:----------:|
| dariush  | dariush<i></i>@example.com | dariush123 |  22  | 2020-12-01 |


**Example**:

`https://example.com?name[0]=mehrad&name[1]=dariush`

Output:

|   name   |           email            |  username  |  age | created_at 
|:--------:|:--------------------------:|:----------:|:----:|:----------:|
| mehrad   | mehrad<i></i>@example.com  | mehrad123  |  20  | 2020-09-01 |
| dariush  | dariush<i></i>@example.com | dariush123 |  22  | 2020-12-01 |

**Example**:

`https://example.com?name[0]=mehrad&name[1]=dariush&username[0]=mehrad123&username[1]=reza1234`

Output:

|   name   |           email            |  username  |  age | created_at 
|:--------:|:--------------------------:|:----------:|:----:|:----------:|
| mehrad   | mehrad<i></i>@example.com  | mehrad123  |  20  | 2020-09-01 |

### Custom Filters
By custom filters you can define your own methods as filters. This helps with the Open/Closed of SOLID principles, Hence each time a new filter is needed, you don't have to edit previous filters and you can just write a separate method for it.

Let's create a custom filter. Assuming you want to create a filter named `all_except` which retrieves all users except the one that is specified:

In User.php
```php
protected $filters = ['all_except'];

public function all_except($query, $value) {
    $query->where('name', '!=', $value);
}
```
To test our newly added filter:

`https://example.com?all_except=mehrad`

Output:

|   name   |           email            |  username  |  age | created_at 
|:--------:|:--------------------------:|:----------:|:----:|:----------:|
| reza     | reza<i></i>@example.com    | reza123    |  20  | 2020-10-01 |
| hossein  | hossein<i></i>@example.com | hossein123 |  22  | 2020-11-01 |
| dariush  | dariush<i></i>@example.com | dariush123 |  22  | 2020-12-01 |

**Note** that your custom defined filters have the most priority which means you can even override available filters.

For example lets change `in` filter in a way that only accepts 3 values:

In User.php
```php
protected $filters = ['in'];

public function in($query, $value) {
    
    $exploded = explode(',', $value);

    if(count($exploded) != 4) {
        // throwing an exception or whatever you like to do
    }

    $field = array_shift($exploded);

    $query->whereIn($field, $exploded);
}
```

**Another** good example for custom filters are when you don't want to expose your database table's column name. For example assume we don't want to expose that we have a column named `username` in `users` table:

In User.php
```php
protected $filters = ['by'];

public function by($query, $value) {
    $query->where('username', $value);
}
```

`https://example.com?by=dariush123`

Output:

|   name   |           email            |  username  |  age | created_at 
|:--------:|:--------------------------:|:----------:|:----:|:----------:|
| dariush  | dariush<i></i>@example.com | dariush123 |  22  | 2020-12-01 |

#### Minor Tip
In order to prevent your model to get messy or populated with filter methods, You can create a trait for it and put everything about filters inside the trait.

You can also take a look at [Laravel Filter Query String](https://github.com/mehradsadeghi/laravel-filter-querystring) GitHub repo.