DEV Community

loading...

How To Filter Your Eloquent Queries Based on URL Query String in Laravel Like a Pro

mehradsadeghi profile image Mehrad Sadeghi ・5 min read

Alt Text

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.
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:
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@example.com mehrad123 20 2020-09-01
2 reza reza@example.com reza123 20 2020-10-01
3 hossein hossein@example.com hossein123 22 2020-11-01
4 dariush dariush@example.com dariush123 22 2020-12-01

And assume our query is something like this:

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

protected $filters = ['sort'];

Single sort:

https://example.com?sort=created_at

Output:

name email username age created_at
mehrad mehrad@example.com mehrad123 20 2020-09-01
reza reza@example.com reza123 20 2020-10-01
hossein hossein@example.com hossein123 22 2020-11-01
dariush dariush@example.com dariush123 22 2020-12-01
  • Note that when you're not defining sort_type, It'll be asc by default.

Multiple sorts:

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

Output:

name email username age created_at
dariush dariush@example.com dariush123 22 2020-12-01
hossein hossein@example.com hossein123 22 2020-11-01
reza reza@example.com reza123 20 2020-10-01
mehrad mehrad@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

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@example.com hossein123 22 2020-11-01
dariush dariush@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@example.com mehrad123 20 2020-09-01
reza reza@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

protected $filters = ['in'];

Example:

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

Output:

name email username age created_at
mehrad mehrad@example.com mehrad123 20 2020-09-01
reza reza@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

protected $filters = ['like'];

Single like:

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

Output:

name email username age created_at
mehrad mehrad@example.com mehrad123 20 2020-09-01

Multiple likes:

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

Output:

name email username age created_at
mehrad mehrad@example.com mehrad123 20 2020-09-01
dariush dariush@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

protected $filters = ['name', 'username', 'age'];

Example:

https://example.com?name=mehrad

Output:

name email username age created_at
mehrad mehrad@example.com mehrad123 20 2020-09-01

Example:

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

Output:

name email username age created_at
dariush dariush@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@example.com mehrad123 20 2020-09-01
dariush dariush@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@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

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@example.com reza123 20 2020-10-01
hossein hossein@example.com hossein123 22 2020-11-01
dariush dariush@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

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

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@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 GitHub repo.

Discussion (0)

pic
Editor guide