DEV Community

Cover image for Laravel 9 Eloquent WHERE Like Query Example Tutorial
Suresh Ramani
Suresh Ramani

Posted on • Originally published at techvblogs.com

Laravel 9 Eloquent WHERE Like Query Example Tutorial

When you put the search form in your application, you need to use like query to get matched pattern. The LIKE a query is used in a WHERE clause to search for a specified pattern in a column. You can use the LIKE MySQL keyword and % wildcard character with where clause.

In laravel, using whereLike() eloquent method, you can implement laravel where like search query, laravel where like multiple columns and laravel collection with where like.

Example 1: Laravel where Like Query use with Eloquent Model

You can use the LIKE MySQL keyword and % wildcard character with the where clause.

The following example represents, how to use it:

public function index(){
    $users = User::where('name','LIKE',"%{$search}%")->get();
    return $users;                
}
Enter fullscreen mode Exit fullscreen mode

When you dump the above given whereNull query you will get the following SQL query:

SELECT * FROM `users` WHERE `name` LIKE '%search%';
Enter fullscreen mode Exit fullscreen mode

Example 2: Using macros with Like

To define a macro, you simply use the macro static method on the class you want to define the macro to. We need to define a macro for the Eloquent class, so we can extend it like this (in the boot method of the service provider):

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

The way we can use this macro now is simple:

public function index(){
  return User::whereLike('username', $username)
   ->whereLike('email', $email)
   ->get();
}
Enter fullscreen mode Exit fullscreen mode

Example 3: Laravel whereLike with multiple columns using macros

if you want to search with multiple columns then you have to extend this macro to support multiple columns.

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;
});
Enter fullscreen mode Exit fullscreen mode

So now, if we pass a single column (using the array_wrap function we convert it to an array), and search that column, but if we add multiple columns in an array then we loop through all of them and search the search term in all of those columns. Everything is wrapped in an where query because we don't want the whereLike query to mess up any other where queries we can perform on the Eloquent model.

You can use this macro now like this:

public function index(){
 return User::whereLike(['username', 'email'], $search)->get();
}
Enter fullscreen mode Exit fullscreen mode

Thank you for reading this article.

Discussion (0)