How to add case insensitive where in Laravel

Solving the Repetitive Query Dilemma: Harnessing the Power of Laravel Eloquent Macros

Have you ever found yourself writing the same complex database queries repeatedly? Yeah, me too. It’s frustrating, time-consuming, and, let’s face it, not the best use of our coding superpowers.

I needed to search some tables for case-insensitive values and discovered that Laravel doesn’t have a native function in eloquent, or it’s entirely possible that I missed it. Today, we will tackle this problem head-on by diving into the world of Laravel Eloquent macros. If you’ve never used them, this will be a game-changer for your productivity.

The Problem: Query Repetition Syndrome

Picture this: You’re working on a project where you frequently need to perform case-insensitive searches across multiple columns. Or maybe you’re constantly writing complex ordering logic to handle null values. Sound familiar?

If you’re nodding your head, you’re not alone. This is what I call “Query Repetition Syndrome,” and it’s a common ailment among Laravel developers.

The Solution: Eloquent Macros to the Rescue

Enter Eloquent macros – your new secret weapon against repetitive queries. These bad boys allow you to extend Eloquent’s query builder with custom methods. It’s like giving your Laravel app query superpowers!

Let’s break down how to implement this solution step-by-step.

Step 1: Create Your Macro Factory

First things first, we need a place to house our macros. Let’s create a new service provider:

php artisan make:provider MacroServiceProvider

Step 2: Set Up the MacroServiceProvider

Open up that freshly minted MacroServiceProvider.php, and let’s give it some structure:


<?php

namespace App\Providers;

use Illuminate\Database\Eloquent\Builder;
use Illuminate\Support\ServiceProvider;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Arr;

class MacroServiceProvider extends ServiceProvider
{
    public function boot(): void
    {
        $this->addWhereInLikeMacro();
        $this->addWhereLikeMacro();
        $this->addOrderByNullsLastMacro();
    }

    // We'll add our macro methods here
}

Step 3: Craft Your First Macro

Let’s tackle that case-insensitive search problem with a whereInLike macro:


protected function addWhereInLikeMacro(): void
{
    Builder::macro('whereInLike', function ($column, array $values, $caseSensitive = false) {
        return $this->where(function ($query) use ($column, $values, $caseSensitive) {
            $column = DB::raw($caseSensitive ? $query->getGrammar()->wrap($column) : 'LOWER(' . $query->getGrammar()->wrap($column) . ')');
            foreach ($values as $value) {
                $method = $query->getQuery()->wheres ? 'orWhere' : 'where';
                $value = $caseSensitive ? "%$value%" : '%' . strtolower($value) . '%';
                $query->$method($column, 'LIKE', DB::raw('?'));
                $query->addBinding($value, 'where');
            }
        });
    });
}

Step 4: Add More Macros to Your Arsenal

While we’re at it, let’s add a couple more handy macros:


protected function addWhereLikeMacro(): void
{
    Builder::macro('whereLike', function ($columns, $value) {
        return $this->where(function ($query) use ($columns, $value) {
            foreach (Arr::wrap($columns) as $column) {
                $query->orWhere(DB::raw('LOWER(' . $query->getGrammar()->wrap($column) . ')'), 'LIKE', '%' . strtolower($value) . '%');
            }
        });
    });
}

protected function addOrderByNullsLastMacro(): void
{
    Builder::macro('orderByNullsLast', function ($column, $direction = 'asc') {
        $column = $this->getGrammar()->wrap($column);
        $direction = strtolower($direction) === 'asc' ? 'asc' : 'desc';
        
        return $this->orderByRaw("CASE WHEN {$column} IS NULL THEN 1 ELSE 0 END, {$column} {$direction}");
    });
}

Step 5: Register Your Macro Provider

Don’t forget to tell Laravel about your new macros. Add this line to your config/app.php:


'providers' => [
    // Other providers...
    App\Providers\MacroServiceProvider::class,
],

Step 6: Unleash Your New Query Superpowers

Now for the fun part – using your new macros:


// Case-insensitive search for multiple values
$users = User::whereInLike('name', ['John', 'Jane', 'Bob'])->get();

// Search across multiple columns
$users = User::whereLike(['name', 'email'], 'john')->get();

// Order results with nulls last
$users = User::orderByNullsLast('last_login')->get();

The Result: Clean, Efficient, and DRY Code

By implementing these Eloquent macros, we’ve solved our Query Repetition Syndrome. Instead of writing the same complex queries over and over, we have reusable, eloquent (pun intended) methods that make our code cleaner and more efficient.

But here’s the kicker – this is just the beginning. You can create macros for any repetitive query pattern in your projects. The possibilities are endless!

Wrapping Up

Eloquent macros are like a Swiss Army knife for your database queries. They’re powerful and flexible and can save you tons of time and headaches.

Remember, the goal here is to work smarter, not harder. By identifying repetitive patterns in your queries and turning them into macros, you’re setting yourself up for cleaner, more maintainable code in the long run.

Now, I’m curious – what repetitive query patterns have you encountered in your Laravel projects? Please drop a comment below to share your macros or share some macro solutions!

Happy coding, and may your queries be ever elegant!

Author