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

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
avatar

Got some error in Laravel 8.83,

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

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.

Webmentions

Thibault Six retweeted on 30th April 2020
Osama Kamel liked on 30th April 2020
Niels liked on 30th April 2020
Juan Pablo 👨🏻‍💻 liked on 30th April 2020
Amir Ahmic liked on 30th April 2020
jed silvestre liked on 30th April 2020
이현석 Hyunseok Lee liked on 30th April 2020
Ravi Bastola  liked on 30th April 2020
Salman Zafar liked on 30th April 2020
Mohammad Nur liked on 29th April 2020
Laravel Türkiye retweeted on 29th April 2020
Laravel Türkiye liked on 29th April 2020
Alberto Garcia Jr A liked on 29th April 2020
Lukáš Neuschl liked on 29th April 2020
Lukáš Neuschl retweeted on 29th April 2020
Daniele Rosario liked on 29th April 2020
Arno Poot liked on 29th April 2020
Nandor Sperl liked on 29th April 2020
Melek Rebai liked on 29th April 2020
बेद retweeted on 29th April 2020
assoftTR retweeted on 29th April 2020
Ahmet Mirzabeyoğlu retweeted on 29th April 2020
assoftTR liked on 29th April 2020
Ahmet Mirzabeyoğlu liked on 29th April 2020
Estern Winluck liked on 29th April 2020
Set Kyar Wa Lar retweeted on 29th April 2020
Ankur Kumar liked on 29th April 2020
Sander de Vos liked on 29th April 2020
Ramazan Toprak liked on 29th April 2020
Bill Yanelli liked on 29th April 2020
Neil Keena retweeted on 29th April 2020
Jose Lara liked on 29th April 2020
Jk Que liked on 29th April 2020
Geoffrey van Wyk liked on 29th April 2020
Neil Carlo Faisan Sucuangco liked on 29th April 2020
Neil Keena liked on 29th April 2020
Lucas Colette liked on 29th April 2020
Matt Kingshott 🚀 liked on 29th April 2020
Faisal ahmed liked on 29th April 2020
Ryan Chandler replied on 29th April 2020
Using ^^^ this on my blog rebuild, works like a charm with Livewire for search!
Matt Kingshott 🚀 replied on 29th April 2020
If you’re interested in doing fuzzy searching, check out my quest package - github.com/mattkingshott/…
Abdelrahman Ibrahim liked on 29th April 2020
Alejandro Rearte liked on 29th April 2020
Nuno Souto liked on 29th April 2020
Jahlien liked on 29th April 2020
Noe liked on 29th April 2020
Simon Blonér liked on 29th April 2020
John Adams liked on 29th April 2020
undefined liked on 29th April 2020
Benjamin Delespierre replied on 29th April 2020
Very elegant ! 😍
DIABATE Saliou retweeted on 29th April 2020
Bezhan Salleh retweeted on 29th April 2020
Gabi Rusu retweeted on 29th April 2020
Mithicher Baro retweeted on 29th April 2020
Asyraf Hussin 👨‍💻 liked on 29th April 2020
Ahmad Ripaldi liked on 29th April 2020
Bezhan Salleh liked on 29th April 2020
Devon Mather liked on 29th April 2020
Mithicher Baro liked on 29th April 2020
Micha Vie-hau-ser liked on 29th April 2020
Rory Willis liked on 29th April 2020
'Tel ♠ liked on 29th April 2020
ServerPilot liked on 29th April 2020
Willan Correia liked on 29th April 2020
Set Kyar Wa Lar liked on 29th April 2020
Kamau Wanyee liked on 29th April 2020
Sheldon Rupp retweeted on 29th April 2020
PHP Synopsis retweeted on 29th April 2020
Tina Hammar liked on 29th April 2020
Saleh liked on 29th April 2020
Ryan Atkins liked on 29th April 2020
Charles Bowen liked on 29th April 2020
LF liked on 29th April 2020
import { TY } from 🌎 liked on 29th April 2020
Federico Corsaro liked on 29th April 2020
eminiarts liked on 29th April 2020
Sheldon Rupp liked on 29th April 2020
woody mendoza liked on 29th April 2020
ChaoticDebris liked on 29th April 2020
Benjamin Delespierre replied on 29th April 2020
Pragmatic indeed. Gimme 5 minutes to understand it tho 😂
Adam 🤙🏼 Mench liked on 29th April 2020
Khai Rahman retweeted on 29th April 2020
Ahmed Amshir retweeted on 29th April 2020
Maarten de Graaf liked on 29th April 2020
ali ali liked on 29th April 2020
Beater liked on 29th April 2020
kanamastaka liked on 29th April 2020
Paulius Jasiulis liked on 29th April 2020
Daniel Lucas liked on 29th April 2020
Cássio Almeida liked on 29th April 2020
Khai Rahman liked on 29th April 2020
ˎˊ˗ liked on 29th April 2020
Yaël 🛸 liked on 29th April 2020
Lucas Fiege liked on 29th April 2020
Adam Bailey liked on 29th April 2020
Tigran Hakobyan liked on 29th April 2020
Lee Overy liked on 29th April 2020
Patric Poba retweeted on 29th April 2020
Marian Pop 🦄 retweeted on 29th April 2020
Bimlesh Tiwari liked on 29th April 2020
Tomáš Máčala liked on 29th April 2020
Alan Rezende liked on 29th April 2020
Mike liked on 29th April 2020
Mohamed AbdElaziz liked on 29th April 2020
Naitik Patel liked on 29th April 2020
Feras Shaer 👨🏽‍💻 liked on 29th April 2020
mahmod liked on 29th April 2020
Ramiro Varandas Jr liked on 29th April 2020
Scorp974 liked on 29th April 2020
Manuel Regidor liked on 29th April 2020
Jelliti Marwen liked on 29th April 2020
Nicolas Giraud liked on 29th April 2020
Peter Brinck 🤘 liked on 29th April 2020
Marian Pop 🦄 liked on 29th April 2020
Lambert Traccard liked on 29th April 2020
Mihai Crăiță replied on 29th April 2020
Somebody loves 🍝
duq retweeted on 29th April 2020
Andre Sayej liked on 29th April 2020
Robin Dirksen liked on 29th April 2020
duq liked on 29th April 2020
Hardik Shah liked on 29th April 2020
Benjamin Eberlei replied on 29th April 2020
not sure if I can convince myself that 4 layers of nested callables are pragmatic ;-)
Freek Van der Herten replied on 29th April 2020
Yeah, point taken 😁
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