Oh Dear is the all-in-one monitoring tool for your entire website. We monitor uptime, SSL certificates, broken links, scheduled tasks and more. You'll get a notifications for us when something's wrong. All that paired with a developer friendly API and kick-ass documentation. O, and you'll also be able to create a public status page under a minute. Start monitoring using our free trial now.

Searching models using a where like query in Laravel

Original – by Freek Van der Herten – 5 minute read

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.

Here's another version by Peter Matseykanets.

And yet another one that can search soft deletes.

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

Happy searching!

Stay up to date with all things Laravel, PHP, and JavaScript.

You can follow me on these platforms:

On all these platforms, regularly share programming tips, and what I myself have learned in ongoing projects.

Every month I send out a newsletter containing lots of interesting stuff for the modern PHP developer.

Expect quick tips & tricks, interesting tutorials, opinions and packages. Because I work with Laravel every day there is an emphasis on that framework.

Rest assured that I will only use your email address to send you the newsletter and will not use it for any other purposes.

Comments

Hamza avatar

This is fantastic, is there anything we can do to not to search spaces? Sometimes users search for ' ' double spaces which causes some issue like (not found).

👍 1
👀 1
Isaac avatar

Got some error in Laravel 8.83,

BadMethodCallException Call to undefined method Illuminate\Database\Query\Builder::orWhereHas()

Praise Dare avatar

I made a small improvement to allow for querying deeply nested relationships on this github gist

Comments powered by Laravel Comments
Want to join the conversation? Log in or create an account to post a comment.