How to Use a WHERE LIKE Condition in Laravel Eloquent

Searching through database records is a common requirement in web applications. Laravel’s Eloquent provides a powerful and efficient way to perform SQL “LIKE” queries, enabling you to search for specific patterns within your database.

To search an Eloquent model for records matching a (partial) string we can use LIKE and SQL wildcards “%” together and fetch results with get(), for example:

Model::where('column', 'like', '%SearchString%')->get();

In this tutorial, I’ll walk you through the steps to use WHERE LIKE in Laravel Eloquent by making an example application. We’ll create a model, migrate a table, seed it with sample data, and demonstrate how to perform LIKE queries using Eloquent.

Let’s get started!

Step 1: Create a Laravel Project

Begin by creating a new Laravel project or use an existing one:

laravel new eloquent-like-tutorial
cd eloquent-like-tutorial

Step 2: Generate Mode and Seeder

Generate a model named Superhero along with its migration and a seeder:

php artisan make:model Superhero -m

Step 3: Add Migration Code

Edit the generated migration file and add the following code to define the table schema for the superheroes table, including columns for name and alias:

database/migrations/2024_01_03_202207_create_superheroes_table.php
<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

return new class extends Migration
{
    /**
     * Run the migrations.
     */
    public function up(): void
    {
        Schema::create('superheroes', function (Blueprint $table) {
            $table->id();
            $table->string('name');
            $table->string('alias');
        });
    }

    /**
     * Reverse the migrations.
     */
    public function down(): void
    {
        Schema::dropIfExists('superheroes');
    }
};

Step 4: Run Migration

Run the migrations to create the superheroes table:

php artisan migrate

Step 5: Add Model Code

Add the following code to the generated Superhero Model so that we can easily insert our data using Eloquent and mass assign.

app/Models/Superhero.php
<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class Superhero extends Model
{
    protected $fillable = ['name', 'alias'];
}

Step 6: Create Seeder

To populate our table we’ll use simple seeder to insert a few records. Let’s create a seeder with artisan by running:

php artisan make:seeder SuperheroSeeder

Step 7: Add Seeder Code

Open the generated file app/database/seeders/SuperheroSeeder.php and add some code to insert demo data. For example:

app/database/seeders/SuperheroSeeder.php
<?php

namespace Database\Seeders;

use App\Models\Superhero;
use Illuminate\Database\Seeder;

class SuperheroSeeder extends Seeder
{
    /**
     * Run the database seeds.
     */
    public function run(): void
    {
        Superhero::insert([
            ['name' => 'Peter Parker', 'alias' => 'Spider-Man'],
            ['name' => 'Steve Rogers', 'alias' => 'Captain America'],
            ['name' => 'Natasha Romanoff', 'alias' => 'Black Widow'],
            ['name' => 'Tony Stark', 'alias' => 'Iron Man'],
            ['name' => 'Bruce Banner', 'alias' => 'The Hulk'],
            // Add more records here
        ]);
    }
}

Note that we’re adding 5 hardcoded records in this seeder. Laravel allows you to easily fill the database with any amount of random data, learn how to do it by reading my post on seeding multiple rows at once with Laravel Factories.

Step 8: Run the Seeder

Run the migrations to create the superheroes table and seed it with sample data:

php artisan db:seed --class=SuperheroSeeder

Our database now contains the following records:

Step 9: Test the Application

Now, let’s put our Superhero model’s searching capabilities to the test using Laravel’s tinker—a command-line tool for running application code without needing additional routes or controllers.

Now, let’s start a tinker shell by running:

php artisan tinker

Inside the tinker shell paste the following code:

use App\Models\Superhero;

// Search in `alias`, will show 2 records
Superhero::where('alias', 'like', '%Man%')->get();

// Search in `name`, will show 1 records
Superhero::where('name', 'like', 'Tony%')->get();

// Search in `name`, will show 0 records
Superhero::where('name', 'like', 'Spider%')->get();

// Search in `name`, will show 0 records
Superhero::where('name', 'like', '%Hulk')->get();

// Search in `alias`, will show 1 records
Superhero::where('alias', 'like', '%Hulk')->get();

The ‘%’ symbol within the like clause acts as a wildcard in SQL, representing zero, one, or multiple characters in a search string. For instance, %Man% matches any string containing ‘Man’ in any position, while Tony% matches any string starting with ‘Tony’.

By examining the output in the tinker shell, you can verify that the displayed results align with the expected data.

Caveats

In MySQL (and many other databases), the default collation is case-insensitive most of the time. In that case the comparisons made with LIKE queries are also performed case-insensitive.

In case you want to support case sensitive searches you can either:

  • use a collation that is case sensitive like for example utf8_bin
  • work around the fact by using LIKE BINARY instead of plain LIKE

When using Laravel with MySQL you can force case sensitive comparisons by slightly rewriting the like statements to:

// 2 results
Superhero::where('alias', 'like binary', '%Man%')->get();

// 0 results
Superhero::where('alias', 'like binary', '%man%')->get();

Conclusion

Great job! You’ve successfully learned how to perform LIKE queries using Laravel Eloquent’s where('column', 'like', 'pattern') method.

This allows you to flexibly search database records for matching content which is useful for example when creating search forms for your endusers.

Now go ahead and apply these queries to search data in Models in your own application. Happy coding!

References:

Johan van den Broek

Johan is the creator of laracoding.com. As a child, he began tinkering with various programming languages, many of which have been long forgotten today. Currently, he works exclusively with PHP, and his passion for programming remains to this day.

Leave a Reply

Your email address will not be published. Required fields are marked *

Recent Posts