Filtering using WHERE with the LIKE operator is very popular in SQL queries. You can use it to find records containing some words or symbols in the database. But Laravel does not have a dedicated method for this operator.
Thus, you should use the where() method and specify the operator. In this short article, I will explain how to use where like in Laravel.
Table of Contents
Using WHERE LIKE in Laravel Tutorial
Prepare Environment
Firstly, let’s prepare an environment for testing. You will need any Laravel project deployed on your computer. Create an articles table with author, title, and content columns:
php artisan make:migration ArticlesTable
<?php
use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;
return new class extends Migration {
public function up()
{
Schema::create("articles", function (Blueprint $table) {
$table->id();
$table->string("title");
$table->string("content");
$table->string("author");
$table->timestamps();
});
}
public function down()
{
Schema::dropIfExists("articles");
}
};
Then, create a model for this table:
php artisan make:model Article
You can leave default content in this class. After this, make a factory which will create a few articles in the table:
php artisan make:factory Article
<?php
namespace Database\Factories;
use Illuminate\Database\Eloquent\Factories\Factory;
class ArticleFactory extends Factory
{
public function definition()
{
return [
"author" => "haait",
"title" => $this->faker->realText(100),
"content" => $this->faker->realText(200),
];
}
}
After this, run migrations:
php artisan migrate
Then, you can create a few records in the articles table using ArticleFactory:
use App\Models\Article;
Article::factory(10)->create();
Using Eloquent where() method
Let’s remember how to use this operator in SQL:
SELECT * FROM articles WHERE text LIKE "%Alice%';
You also can use case insensitive version of this operator:
SELECT * FROM articles WHERE text LIKE "%Alice%';
As you can see, here you must wrap a value into the wildcard symbols if you want to find occurrences of a word in a field, but not only matches of the word to the entire value of the field. In this example, I used the ‘%’ symbol which replaces any number of any characters. It is the most used wildcard symbols with the LIKE operator.
When you want to build a SQL query with WHERE LIKE clause in Laravel, you must use where() method and pass three arguments into it: field name, operator (LIKE or ILIKE), and the value for searching wrapped by % or other wildcard symbols. For example:
$articles = Article::query()
->where("title", "LIKE", "%Alice%")
->get()
->toArray();
dd($articles);
The total number of records is 20, but this query found only 8. You see only records that contain the word ‘Alice’.
Creating custom whereLike() method
The operator and wildcard pattern may vary in different cases. This maybe the reason why Laravel developers do not add a dedicated method for the LIKE operator. But you can do it yourself if you know you will use the same pattern. The Illuminate\Database\Eloquent\Builder class has the macro() static method which allows to extend Eloquent functionality. Add this code into App\Providers\AppServiceProvider::boot() method:
Builder::macro("whereLike", function (
string $column,
string $term,
$boolean = "and"
) {
return $this->where($column, "LIKE", "%" . $term . "%", $boolean);
});
Builder::macro("orWhereLike", function (string $column, string $term) {
return $this->orWhere($column, "LIKE", "%" . $term . "%");
});
The Builder::macro() method replaces the $this reference with the current Eloquent Builder instance, so you can use it to interact with the builder. This code adds two methods, whereLike() and orWhereLike(). The first method combines the current condition with other WHERE clauses by AND operator as same as the regular where() method works. The second method works as well as the orWhere() Eloquent method. For example, if you want to get all records which contain Alice and Queen use this code:
$articles = Article::query()
->whereLike("content", "Alice")
->whereLike("content", "Queen")
->get()
->toArray();
dd($articles);
But if you want to find records which contain Alice or Queen, use the orWhereLike() method:
$articles = Article::query()
->whereLike("content", "Alice")
->orWhereLike("content", "Queen")
->get()
->toArray();
dd($articles);
You can read more about combining Eloquent conditions in the article How to Use Where() in Laravel.
Wrapping Up
In this article, I have explained how to use where like in Laravel using standard methods from Eloquent query builder or how to create your own methods to make using of this operator more convenient. Which way do you prefer? Or maybe you know another solution? Tell about it in the comments section below!
