★ Searching models using a where like query in Laravel

For a project I'm working on I needed to build a lightweight, pragmatic search. In this blogpost I'd like to go over my solution.

Searching Eloquent models

Imagine you need to provide a search for users. Using Eloquent you can perform a search like this:

User::query()
   ->where('name', 'LIKE', "%{$searchTerm}%") 
   ->orWhere('email', 'LIKE', "%{$searchTerm}%") 
   ->get();

This will return all records that have a name or email that contains the string in $searchTerm. If you're using MySQL this search will also be perform in a case insensitive way, which is probably what your want.

Using a macro

Now, if you want to add a search, not only the User model but to every model, you can add a macro to the Eloquent's Query builder. If you've never heard of a macro in Laravel read this excellent blogpost on the Tighten Co blog.

Here's how our macro could look like. You can put it the boot method of App\Providers\AppServiceProvider or a service provider of your own.

use Illuminate\Database\Eloquent\Builder;

// ...

Builder::macro('whereLike', function(string $attribute, string $searchTerm) {
   return $this->orWhere($attribute, 'LIKE', "%{$searchTerm}%");
});

We can now search our model like this:

User::query()
   ->whereLike('name', $searchTerm)
   ->whereLike('email', $searchTerm)
   ->get();

Improving our macro

There's still room for improvement. I don't like the fact that we now have to repeat that whereLike call for every attribute we want to search. Let's fix that. Here's an improved version of our macro.

Builder::macro('whereLike', function($attributes, string $searchTerm) {
   foreach(array_wrap($attributes) as $attribute) {
      $this->orWhere($attribute, 'LIKE', "%{$searchTerm}%");
   }

   return $this;
});

array_wrap is a nice little Laravel helper. When given an array it just returns that array. When given something else it will wrap it in an array. So you know the result will always be an array.

The macro above can be used like this:

// searching a single column
User::whereLike('name', $searchTerm)->get();

// searching multiple columns in one go
User::whereLike(['name', 'email'], $searchTerm)->get();

Fixing our macro

Our macro already looks pretty good, but it has a nasty bug.

Consider this query:

User::query()
   ->where('role', 'admin')
   ->whereLike(['name', 'email'], 'john')
   ->get();

If you think this will return only users with an admin role you're mistaken. Because our whereLike macro contains orWhere this will return each user with an admin role and all users whose name or email contain john.

Let's fix that by wrapping our orWheres in a function. This is the equivalent of setting brackets in the search query.

Builder::macro('whereLike', function ($attributes, string $searchTerm) {
    $this->where(function (Builder $query) use ($attributes, $searchTerm) {
        foreach (array_wrap($attributes) as $attribute) {
            $query->orWhere($attribute, 'LIKE', "%{$searchTerm}%");
        }
    });

    return $this;
});

Now the query above will return all admins whose name or email contain john.

Adding support for relations

As it stand we can now only search the attributes of the model where using the scope on. Let's add support for searching the attributes of the relations of that model as well.

Builder::macro('whereLike', function ($attributes, string $searchTerm) {
    $this->where(function (Builder $query) use ($attributes, $searchTerm) {
        foreach (array_wrap($attributes) as $attribute) {
            $query->when(
                str_contains($attribute, '.'),
                function (Builder $query) use ($attribute, $searchTerm) {
                    [$relationName, $relationAttribute] = explode('.', $attribute);

                    $query->orWhereHas($relationName, function (Builder $query) use ($relationAttribute, $searchTerm) {
                        $query->where($relationAttribute, 'LIKE', "%{$searchTerm}%");
                    });
                },
                function (Builder $query) use ($attribute, $searchTerm) {
                    $query->orWhere($attribute, 'LIKE', "%{$searchTerm}%");
                }
            );
        }
    });

    return $this;
});

With that macro can do something like this:

Post::whereLike(['name', 'text', 'author.name', 'tags.name'], $searchTerm)->get();

In closing

The above macro does perfectly what I need in my project. But you might take it further. Here's a variation made by Sergio Bruder that splits the search terms.

Based on @freekmurze search macro, this version splits the search term so you can search for “Sergio Bruder” and find “Sergio Devojno Bruder”. Search terms are AND’ed per field and OR’ed between fields. so it will not find “Sergio Else” name and “[e-mail]” mail. #laravel pic.twitter.com/YxvmRRw16P

— Sergio Bruder (@sdbruder) October 20, 2018

Here's another version by Peter Matseykanets.

I'd suggest the fourth one ? to

• make the name more explicit (helpful if already using Scout )
• allow arbitrary patterns by not forcing % wildcard chars around the term(s)
• allow searching for multiple terms in the same or multiple attributes pic.twitter.com/PKgdIBCa0G

— Peter Matseykanets (@pmatseykanets ) October 19, 2018

And yet another one that can search soft deletes.

Tweaked @freekmurze's search macro a bit. With this, you can also make searches in soft deleted entries by passing true as the third argument in the whereLike method.

e.g. Post::whereLike(['name', 'text', 'https://t.co/kyWEu76VDt'], $searchTerm, true)->get(); pic.twitter.com/kSzvDxtPbW

— Mecit (@TheyWillRepent) 31 oktober 2018

There are many options if you need a more advanced search. Here are some of them:

• Laravel Scout
• Elasticsearch
• Algolia

Happy searching!