use 'where like' in Laravel

How to use ‘where like’ in Laravel 8?

by

When using a search form in an application, we often need to use the like query to match patterns. If we need to search for a specified pattern, we use the LIKE query along with a WHERE clause. There are many ways we can use the whereLike() method in Laravel. This makes it possible for us to implement a search query in Laravel application itself. Let’s see how we can use ‘where like’ on single and multiple columns in a Laravel application.

Method 1: Use ‘where like’ with Eloquent model.

We can use the LIKE keyword and % wildcard character with the where clause, as shown below.

public function index(){
    $customers= Customer::where('name','LIKE',"%{$search}%")->get();
    return $customers;                
}

We apply the query on the Customer model. The where function here searches for results where name matched patterns of the entered search keyword. The above query in SQL looks like. All results are returned in the $customers variable and displayed in table of the connected view blade.

SELECT * FROM `customers` WHERE `name` LIKE '%search%';

Method 2: Use macros with like

We can define a macro using the macro static method. We define macros for an Eloquent class and extend it as shown below.

Builder::macro('whereLike', function($column, $search) {
  return $this->where($column, 'LIKE', "%{$search}%");
});

Then we can use the macro in the index function as follows.

public function index(){
  return Customer::whereLike('name', $name)
   ->whereLike('email', $email)
   ->get();
}

Here the whereLike method is used on the Customers model. We can pass as many whereLike criteria and get the results.

Method 3:’where like’ with multiple columns using macros

We can use the macros method with multiple columns. We will extend the macro to add multiple columns support.

Builder::macro('whereLike', function($columns, $search) {
  $this->where(function($query) use ($columns, $search) {
    foreach(\Arr::wrap($columns) as $column) {
      $query->orWhere($column, $search);
    }
  });

  return $this;
});

Now, we can pass a single column (using the array_wrap function that converts it to an array), and search that column. However, if we add multiple columns in an array then we can loop through all of them and search a specific term in all of those columns. Everything is wrapped in the where query as we avoid the whereLike query to mess up other where queries we can perform on the Eloquent model. We will now use the macro as follows.

public function index(){
 return Customer::whereLike(['name', 'email'], $search)->get();
}

This enables search results from multiple columns, i.e. name and email in this case.