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 orWhere
s 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 “bruder@email.com” 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
— Peter Matseykanets (@pmatseykanets) October 19, 2018
- 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
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.
— Mecit (@TheyWillRepent) 31 oktober 2018
e.g. Post::whereLike(['name', 'text', 'https://t.co/kyWEu76VDt'], $searchTerm, true)->get(); pic.twitter.com/kSzvDxtPbW
There are many options if you need a more advanced search. Here are some of them:
Happy searching!
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).
Got some error in Laravel 8.83,
BadMethodCallException Call to undefined method Illuminate\Database\Query\Builder::orWhereHas()
I made a small improvement to allow for querying deeply nested relationships on this github gist