Michael Dyrynda | Filtering models with Eloquent in Laravel

Say you have a users table with the following fields in it name, email, city, state, zip. You may want to provide fuzzy searching for the name, email, or city and exact matching for the state and zipfields. Why fuzzy matching for only some of the fields? Well, you might want to search for everyone whose name contains Michael or has has an @gmail.com address. Be mindful of the latter; it will expose a large dataset if you're not careful in restricting access to the functionality. You probably wouldn't want to allow it in anything bigger than a proof of concept (which this is!).

Let's setup our user model, then:

1use Illuminate\Database\Eloquent\Model;
2 
3class User extends Model {
4 
5    protected $fillable = [ 'name', 'email', 'city', 'state', 'zip', ];
6 
7}

Nothing out of the ordinary here, and we can start interacting with our model any way we normally would:

 1use App\User;
 2 
 3class UsersController extends Controller {
 4 
 5    public function index()
 6    {
 7        $users = User::all();
 8        $users = User::where('name', 'LIKE', 'Michael%')->get();
 9        // and so on
10    }
11 
12}

What if we want to be able to refine the users we return? Say we provide a form in our index view (a simple table) that allows us to filter on the name, email, and so forth. Easy, add the checks into our controller, right?

 1use App\User;
 2use Input;
 3 
 4class UsersController extends Controller {
 5 
 6    public function index()
 7    {
 8        $users = new User;
 9 
10        if ( Input::has('name') && trim(Input::get('name')) !== '' )
11        {
12            $users = $users->where('name', 'LIKE', trim(Input::get('name')) . '%');
13        }
14 
15        $users = $users->get();
16 
17        return view('index', compact('users'));
18    }
19 
20}

As you can imagine, this is going to get quite messy when we add in email, city, state, and zip. Not only that, what happens when you want to reuse the filtering logic in another method in the UsersController, or even in some other part of your application?

Enter query scopes!

Scopes allow you to easily re-use query logic in your models. To define a scope, simply prefix a model method with scope.

 1use Illuminate\Database\Eloquent\Model;
 2use Input;
 3 
 4class User extends Model {
 5 
 6    protected $fillable = [ 'name', 'email', 'city', 'state', 'zip', ];
 7 
 8 
 9    public function scopeFilter($query)
10    {
11        if ( Input::has('name') && trim(Input::get('name') !== '') ) {
12            $query->where('name', 'LIKE', trim(Input::get('name')) . '%');
13        }
14 
15        return $query;
16    }
17 
18}

Now you can update your UsersController to leverage the scopeFilter method:

 1use App\User;
 2use Input;
 3 
 4class UsersController extends Controller {
 5 
 6    public function index()
 7    {
 8        $users = User::filter()->get();
 9 
10        return view('index', compact('users'));
11    }
12 
13}

Now, you likely won't be handling Input directly in your eloquent model. This is something that is better served within your controller. In this example I'm using Input::all() to pass all input keys to our filter scope. You don't have to be too worried about users passing arbitrary keys into the filter as the query scope we define will still only look for specific keys on which to filter.

In your frontend, you may choose to restrict what filter variables are allowed using Input::only() to prevent spitting out all users with an @gmail.com address - it's entirely up to you.

 1use App\User;
 2use Input;
 3 
 4class UsersController extends Controller {
 5 
 6    public function index()
 7    {
 8        $users = User::filter(Input::all())->get();
 9 
10        return view('index', compact('users'));
11    }
12 
13}

Next, we adjust the User model slightly to use the passed parameters:

 1<?php
 2 
 3use Illuminate\Database\Eloquent\Model;
 4 
 5class User extends Model {
 6 
 7    protected $fillable = [ 'name', 'email', 'city', 'state', 'zip', ];
 8 
 9 
10    public function scopeFilter($query, $params)
11    {
12        if ( isset($params['name']) && trim($params['name']) !== '' )
13        {
14            $query->where('name', 'LIKE', trim($params['name'] . '%'));
15        }
16 
17        if ( isset($params['state']) && trim($params['state']) !== '' )
18        {
19            $query->where('state', '=', trim($params['state']));
20        }
21 
22        return $query;
23    }
24 
25}

Something to note here is that the way the query scope is setup will perform an AND search i.e. SELECT * FROM users WHERE name LIKE "<name>%" AND state = "<state>". How you chain the WHERE conditions together will depend on what you're trying to achieve - do you want all users whose name begins with Michael AND live in state South Australia or do you want all uses whose name begins with Michael OR live in state South Australia? This will depend on business needs.

Also to note, is that Eloquent will figure our if you only have one where or orWhere condition and create the query correctly for you.

Now the power you're presented with is that you can add additional parameters by which to filter or change the way you do filtering - from where to orWhere or something else entirely - across every use of the scopeFilter in one place.

Further reading