Oh Dear! monitors your entire website, not just the homepage. You'll get a notification as soon as your website is down, a monthly uptime report, a warning a few days before your SSL certificate expires and much more! Start your free 10 day 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.

Follow me on Twitter. I regularly tweet out programming tips, and what I myself have learned in ongoing projects.

Every two weeks 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.

Linka Softwares retweeted on 31st December 2019
Linka Softwares liked on 31st December 2019
Bader liked on 30th December 2019
Simon Rigby liked on 30th December 2019
Franz replied on 30th December 2019
Nice little abstraction! Isn't this vulnerable to SQL Wildcard Injections, though (owasp.org/index.php/Test…)? I would suggest escaping the wildcards in the user-entered search terms.
Brocard Jr. 🐘 liked on 30th December 2019
Brocard Jr. 🐘 retweeted on 30th December 2019
Chema Trigueros liked on 29th December 2019
Ray Anthony Madrona liked on 29th December 2019
Edilson Cichon liked on 29th December 2019
JJ mas não o Abrams liked on 29th December 2019
Bahaa Alhagar 🐘 retweeted on 29th December 2019
Roman Pronskiy liked on 29th December 2019
Teddy Jimenez Morrobel liked on 29th December 2019
Claudson Martins liked on 28th December 2019
Tahir Raza retweeted on 28th December 2019
Tahir Raza liked on 28th December 2019
Lucas Fiege liked on 28th December 2019
Sebastian Huber liked on 28th December 2019
Vincent Lejtzén liked on 28th December 2019
Faisal ahmed liked on 28th December 2019
Héctor Agüero liked on 28th December 2019
Nick retweeted on 28th December 2019
Irineu M Junior liked on 28th December 2019
Nick liked on 28th December 2019
Lee Overy liked on 28th December 2019
Laravel Leeds retweeted on 28th December 2019
Lukáš Neuschl retweeted on 28th December 2019
Laravel Leeds liked on 28th December 2019
Burhan liked on 28th December 2019
alaa m Ibrahim liked on 28th December 2019
Lukáš Neuschl liked on 28th December 2019
Craig Potter liked on 28th December 2019
Reno Philibert liked on 28th December 2019
Francisco Junior liked on 28th December 2019
Mark Andrew Kato retweeted on 28th December 2019
Nyan Lynn Htut liked on 28th December 2019
Mark Andrew Kato liked on 28th December 2019
Emmanuel liked on 28th December 2019
Nasirou Wagana retweeted on 28th December 2019
Nasirou Wagana liked on 28th December 2019
Mike liked on 28th December 2019
Luca Rossi retweeted on 28th December 2019
Luca Rossi liked on 28th December 2019
Stefan Tanevski liked on 28th December 2019
Felipe Dalcin liked on 28th December 2019
Mior Muhammad Zaki retweeted on 28th December 2019
Mior Muhammad Zaki liked on 28th December 2019
Daan liked on 28th December 2019
Vincent - Développeur liked on 28th December 2019
Madalin Tache liked on 27th December 2019
Mihai Crăiță liked on 27th December 2019
abdujuk liked on 27th December 2019
im.mister.j liked on 27th December 2019
therouv liked on 27th December 2019
gary liked on 27th December 2019
megabyte blue liked on 27th December 2019
David Alexander liked on 27th December 2019
Steve McDougall replied on 27th December 2019
That's quite nice! I built a nice and simple trait that iterates over Model properties for this which was clean and simple to use
ArielMejiaDev retweeted on 27th December 2019
ArielMejiaDev liked on 27th December 2019
usama muneer liked on 27th December 2019
Edwin I Arellano liked on 27th December 2019
Francisco Barreto retweeted on 27th December 2019
Sergio Bruder retweeted on 27th December 2019
Manojkiran retweeted on 27th December 2019
Salman Zafar retweeted on 27th December 2019
SouthDreamz liked on 27th December 2019
Sergio Bruder liked on 27th December 2019
Francisco Barreto liked on 27th December 2019
Stefan Beierl liked on 27th December 2019
Manojkiran liked on 27th December 2019
Bill Yanelli 🤔 liked on 27th December 2019
Enzo Notario liked on 27th December 2019
M. Vug liked on 27th December 2019
Salman Zafar liked on 27th December 2019
Laravel Türkiye retweeted on 27th December 2019
Syahrin retweeted on 27th December 2019
Laravel Türkiye liked on 27th December 2019
AA liked on 27th December 2019
Sinan Eldem liked on 27th December 2019
Ivars liked on 27th December 2019
Omar Andrés Barbosa Ortiz liked on 27th December 2019
BiasSatan liked on 27th December 2019
Freek Van der Herten replied on 27th December 2019
Looks nice! 👍
Johannes Pichler retweeted on 27th December 2019
ali ali liked on 27th December 2019
. liked on 27th December 2019
Ralph Morris liked on 27th December 2019
Ⓜ️ fuz liked on 27th December 2019
Rob Mills liked on 27th December 2019
Sumon Molla Selim liked on 27th December 2019
Johannes Pichler liked on 27th December 2019
Lee Stables liked on 27th December 2019
Mior Muhammad Zaki replied on 27th December 2019
I been opting to github.com/laravie/query-… recently.
Hameed Rahamathullah retweeted on 27th December 2019
Shawn Hooper retweeted on 27th December 2019
Jérémy DECOOL liked on 27th December 2019
Zubair Mohsin liked on 27th December 2019
Feras Shaer 🇵🇸 liked on 27th December 2019
Wyatt liked on 27th December 2019
Yannick Yayo liked on 27th December 2019
Shawn Hooper liked on 27th December 2019
rodel cabugon retweeted on 27th December 2019
Milos Nicic liked on 27th December 2019
leunggamciu retweeted on 27th December 2019
Steve Bauman liked on 27th December 2019
Thomas Bailly liked on 27th December 2019
Grant Williams liked on 27th December 2019
Choirool liked on 27th December 2019
Neil Keena retweeted on 27th December 2019
KAI retweeted on 27th December 2019
Jimmy Lipham liked on 27th December 2019
Rati Wannapanop liked on 27th December 2019
Ravidev liked on 27th December 2019
Bardhyl Fejzullahu liked on 27th December 2019
Sander de Vos liked on 27th December 2019
Jens Twesmann liked on 27th December 2019
Erick Patrick liked on 27th December 2019
Neil Keena liked on 27th December 2019
Sebastian Müller replied on 27th December 2019
Thanks for the article. To keep the example consistent it should be email, should it not? :-)
hjtshort retweeted on 27th December 2019
aldo liked on 27th December 2019
Konafets liked on 27th December 2019
James Hemery liked on 27th December 2019
Cameron Scott liked on 27th December 2019
Mickaël Isaert liked on 27th December 2019
Neil Carlo Faisan Sucuangco liked on 27th December 2019
Diovane Monteiro liked on 27th December 2019
Benjamin Crozat liked on 27th December 2019
Miguel Orellana liked on 27th December 2019
Joshua Gigg replied on 27th December 2019
Doesn't the MySQL case insensitivity depend on the collation of the column? The ci/cs at the end.
James Freeman liked on 27th December 2019
kronos_I retweeted on 27th December 2019
PHP Synopsis retweeted on 27th December 2019
Jamal liked on 27th December 2019
harun thuo liked on 27th December 2019
Florian Voutzinos ⚡ liked on 27th December 2019