★ Searching models using a where like query in Laravel
Tags: laravel, php, eloquent
Published: 2020-01-02 23:05:15

---
title: ★ Searching models using a where like query in Laravel
published: true
date: 2018-10-22 10:00:00 UTC
tags: laravel, php, eloquent
canonical_url: https://freek.dev/1182-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](https://tighten.co/blog/the-magic-of-laravel-macros).

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](https://twitter.com/freekmurze?ref_src=twsrc%5Etfw) 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](https://twitter.com/hashtag/laravel?src=hash&ref_src=twsrc%5Etfw) [pic.twitter.com/YxvmRRw16P](https://t.co/YxvmRRw16P)
> 
> — Sergio Bruder (@sdbruder) [October 20, 2018](https://twitter.com/sdbruder/status/1053719431602466816?ref_src=twsrc%5Etfw)

<script async src="https://platform.twitter.com/widgets.js" charset="utf-8"></script>

Here's another version by Peter Matseykanets.

> I'd suggest the fourth one ? to  
> - 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](https://t.co/PKgdIBCa0G)
> 
> — Peter Matseykanets (@pmatseykanets) [October 19, 2018](https://twitter.com/pmatseykanets/status/1053381531962654721?ref_src=twsrc%5Etfw)

<script async src="https://platform.twitter.com/widgets.js" charset="utf-8"></script>

And yet another one that can search soft deletes.

> Tweaked [@freekmurze](https://twitter.com/freekmurze?ref_src=twsrc%5Etfw)'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.   
>   
> e.g. Post::whereLike(['name', 'text', '[https://t.co/kyWEu76VDt](https://t.co/kyWEu76VDt)'], $searchTerm, true)-\>get(); [pic.twitter.com/kSzvDxtPbW](https://t.co/kSzvDxtPbW)
> 
> — Mecit (@TheyWillRepent) [31 oktober 2018](https://twitter.com/TheyWillRepent/status/1057742260488585216?ref_src=twsrc%5Etfw)

<script async src="https://platform.twitter.com/widgets.js" charset="utf-8"></script>

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

- [Laravel Scout](https://laravel.com/docs/5.7/scout)
- [Elasticsearch](https://www.elastic.co/products/elasticsearch)
- [Algolia](https://www.algolia.com)

Happy searching!