WhereLikeIn() Helper

If you've ever wished you could utilize the efficiency and effectiveness of a SQL where in mixed together with a where like, and you use Laravel, you've come to the right place!

In this post, I'm going to show you how to quickly and effectively build your own whereLikeIn() macro in Laravel. This new macro will be usable across your project as if it existed within Laravel's core framework.

(For those of you not utilizing Laravel, you may be able to extract some key concepts used here and apply them to your own project, but proceed at your own risk!)

Searching Across Multiple Columns

I was working on a new search page for Products, looking at a new feature request that included a "keywords" search. My customers wanted something that allowed a user to search "name" and "description" columns using any number of words. What I wanted was something like Laravel's query builder whereIn(), which is great for searching through a table for matching ids or something similar, but is absolutely useless if you need to compare strings.

I knew I could create a decent (if lengthy) function to split out the search terms and loop through both columns to try and find matching words. However, I realized that this was maybe the first among many requests for a similar search feature, and wanted something that could be accessed across the entire project.

Creating your new whereLikeIn() macro

So here we are, needing a combination of where, like and in and wanting it to be widely available in our project. No worries! First, you'll need to add something like this in the boot method of one of your registered service providers, such as AppServiceProvider.php. It could look something like this:

use Illuminate\Support\ServiceProvider;
use Illuminate\Database\Query\Builder;

class AppServiceProvider extends ServiceProvider
{
    public function boot()
    {
        Builder::macro('whereLikeIn', function ($columns, $searchTerms) {
            $this->where(function (Builder $query) use ($columns, $searchTerms) {
                foreach (Arr::wrap($columns) as $column) {
                    $query->orWhere(function ($query) use ($column, $searchTerms) {
                        foreach ($searchTerms as $searchTerm) {
                            $query->where($column, 'LIKE', $searchTerm);
                        }
                    });
                }
            });
            return $this;
        });
    }
}

This macro whereLikeIn extends Laravel's Query Builder with a new method, performing LIKE comparisons on multiple columns with multiple search terms clearly and efficiently.

The function above takes two parameters:

1. $columns - This can be a single column name or an array of column names.
2. $searchTerms - An array of search terms to filter through.

How does it work?

This macro loops through the columns and search terms you send, finding all records from a given table that fit the terms sent. Here's a breakdown of what's happening:

1. A where clause group is created via $this->where. This group will verify that all of the generated like comparisons are logically grouped together.
2. For each column in the $columns variable, another where clause group is created using $query->orWhere. This will confirm that the query will return true if any of the columns match (not all) if multiple columns are searched.
3. For each search term in the $searchTerms variable, it adds a LIKE clause to the query for the current column using $query->where. This adds multiple LIKE comparisons for each column if multiple search terms are passed.

Using your new whereLikeIn() macro

Here are a few ideas of how you could use this macro in php.

Example - Simply put

$query->whereLikeIn(['name', 'email'], ['%robyn%', '%hood%']);

If you were to look at this generated in SQL, it would look like this:

WHERE (
    (name LIKE '%robyn%' OR name LIKE '%hood%')
    OR
    (email LIKE '%robyn%' OR email LIKE '%hood%')
)

Example - More elaborate scope

Here's an example of how to use your new macro in a scope function. In the function below, I sort through both the "name" and "description" columns of a product model using multiple search terms.

namespace App;

use Illuminate\Database\Eloquent\Builder;

class Product extends Model
{
    /**
     * Filter products by name/description keywords
     *
     * @return Illuminate/Database/Eloquent/Builder
     */
    public function scopeHasKeywordsLike(Builder $builder, string $keywords): Builder
    {
        // Separate the string of keywords/search terms sent into an array
        $wordArray = explode(" ", $keywords);

        // Append % % to each word to search anywhere in the string
        $likeWords = array_map(function ($word) {
            return '%' . $word . '%';
        }, $wordArray);

        // Return builder that is checking name/description columns that are like all terms sent
        return $builder->whereLikeIn(['name', 'description'], $likeWords);
    }
}

The function above uses two parameters:

1. $builder - The built in scope function's Eloquent/Builder - learn more about scopes here!
2. $keywords - A string of words or terms the user wants to use to filter through records.

Note: In this scope, we're adding % to each of the words, because some products have an identifier built in which can be inferred based on the order and product type. So, for example, if the user wishes to search for all Wood products on order 123456 that are treads on staircase A, they could search "Tread W123456A" and the scope function above would return all matching products, even if they include "Tread W123456A01", "Tread W123456A02" and so on. You may want to adjust your scope or macro to suit your own needs.

If you want to append % at the beginning and end of a search term within your whereLikeIn macro, you could build it like this instead:

Builder::macro('whereLikeIn', function ($columns, $searchTerms) {
    $this->where(function (Builder $query) use ($columns, $searchTerms) {
        foreach (Arr::wrap($columns) as $column) {
            $query->orWhere(function ($query) use ($column, $searchTerms) {
                foreach ($searchTerms as $searchTerm) {
                    $query->orWhere($column, 'LIKE', '%' . $searchTerm . '%');
                }
            });
        }
    });
    return $this;
});

Subscribe for exclusive content