Search Eloquent Macro

2018-10-22 Nerd Software sergio

Based on an interesting macro that @FreekMurze published on Twitter and later on his blog, I extended it first by multiplying the number of LIKE’s by the number of whole words in the search string and later on going back to the original logic, only substituting spaces in the search string for “%” instead.

That new version permits to search for “AAH BEE” and find “AAH ANYTHING BEE” but respecting order (it will not find “BEE AAH”), which appears to me to be a nicer implementation.

As someone on twitter already said, beware, this is a index-killer SQL condition: Its full of LIKE “%something%something%”, which is un-indexable.

But if you want to provide a handy search function to your end users, this macro is really handy and users love the functionality. It’s not a full text search, but will do the trick without any complex infra.

// Add it to the boot method of yout Service Provider
Builder::macro('search', function ($attributes, string $searchTerm) {
    $searchTerm = str_replace(' ','%', $searchTerm);
    $this->where(function (Builder $query) use ($attributes, $searchTerm) {
        foreach (array_wrap($attributes) as $attribute) {
            $query->orWhere($attribute, 'LIKE', "%{$searchTerm}%");
        }
    });
    return $this;
});

// Example in tinker:
>>> User::search(['name', 'email'], 'Ornela Neto')->get();
=>Illuminate\Database\Eloquent\Collection {#3100
    all : [
        App\User {#3113
            id : 1,
            name : "Sra. Ornela Correia Escobar Neto",
            email : "[email protected]",
            email_verified_at : null,
            password : "<<PASSWORD>>",
            created_at : "2018-10-21 01:48:44",
            updated_at : "2018-10-21 01:48:44",
        },
    ],
}