Laravel's whereLike method: Simplify Queries

Laravel 11 introduces a new whereLike method to the Query Builder. Let's take a look at how this new addition and ses how it can simplify your database queries and make them safer.

The LIKE Query Conundrum

Ever found yourself wrestling with LIKE queries across different databases? You're not alone. Each database system has its own quirks when it comes to case sensitivity:

• PostgreSQL: ilike for case-insensitive, like for case-sensitive
• MySQL: like for case-insensitive, like binary for case-sensitive
• SQLite: like for case-insensitive, no built-in case-sensitive option
• SQL Server: Relies on database/column collation

In my projects, I've always used PostgreSQL for production and in-memory SQLite for testing. With Laravel 11 making SQLite the default database for new apps, we'll likely see more developers following a similar pattern: SQLite for local development, and MySQL or PostgreSQL in production. This mix of databases can lead you to write messy, conditional code to handle each system's quirks. No fun.

Enter whereLike: The Query Simplifier

To tackle this, I've added four new methods to Laravel's Query Builder:

1. whereLike($column, $value, $caseSensitive = false)
2. whereNotLike($column, $value, $caseSensitive = false)
3. orWhereLike($column, $value, $caseSensitive = false)
4. orWhereNotLike($column, $value, $caseSensitive = false)

These methods work across all databases, handling case sensitivity with a simple boolean flag.

Before and After: A Query Makeover

Let's say you're searching for a user by email. Here's the before and after:

Before:

$users = DB::table('users')
    ->when(DB::getDriverName() === 'pgsql',
        fn($query) => $query->where('email', 'ilike', 'john.doe@example.com'),
        fn($query) => $query->where('email', 'like', 'john.doe@example.com')
    )->get();

After:

$users = DB::table('users')
    ->whereLike('email', 'john.doe@example.com') 
    ->get();

Want case sensitivity? Just add true as the third parameter:

  ->whereLike('email', 'John.Doe@example.com', true)

Here are some more examples using the other new methods orWhereLike, whereNotLike, and orWhereNotLike:

$users = DB::table('users')
	->where('votes', '>', 100)
	->orWhereLike('name', '%John%', true) 
	->get();

$users = DB::table('users')
	->whereNotLike('name', '%John%') 
	->get();

$users = DB::table('users')
	->where('votes', '>', 100)
	->orWhereNotLike('name', '%John%') 
	->get();

Why You'll Love whereLike

1. Clean Code: Say goodbye to database-specific conditionals.
2. Database Agnostic: Works seamlessly across MySQL, PostgreSQL, SQLite, and SQL Server.
3. Explicit Control: Case sensitivity is now a clear, optional parameter.
4. Maintainability: Less code means fewer bugs and easier maintenance.

The Magic Behind the Scenes

Implementing whereLike required some database-specific trickery:

• PostgreSQL: Uses ilike or like
• MySQL: Switches between like and like binary
• SQLite: Switches between like and a custom glob implementation for case-sensitive searches
• SQL Server: Throws an exception for case-sensitive requests (collation-based)

Wrapping Up

The whereLike method is now part of Laravel's core, ready to simplify your database queries. Give it a try and let me what you think!

Want to learn more? Here are some resources for you:

• Laravel's documentation of the whereLike query clause
• Laravel's YouTube channel showcasing the new feature

Happy coding!