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('name', $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.