Laravel Eloquent Macro: Case-Insensitive Multi-Word, Multi-Field & Relationship Search

README.md

Laravel Eloquent Macro: searchIn

A case-insensitive, multi-word, multi-field and relationship search macro for Laravel Eloquent models.

• 🔎 Searches in any number of columns, including relationships (e.g. profession.name)
• ✨ Supports multiple search words (e.g. flav game matches "Flavius Constantin, Game Developer")
• ⚡️ Works for both MySQL/MariaDB and PostgreSQL, with automatic driver detection
• 🥇 No external package required, pure Eloquent!

Usage Example

Profile::query()
    ->searchIn(['first_name', 'last_name', 'username', 'profession.name'], 'flav game')
    ->get();

EloquentSearchInMacro.php

<?php
use Illuminate\Database\Eloquent\Builder as EloquentBuilder;
use Illuminate\Support\Facades\DB;

/**
 * Adds a 'searchIn' macro to Eloquent Builder.
 * 
 * Example:
 * Profile::query()
 *     ->searchIn(['first_name', 'last_name', 'username', 'profession.name'], $search)
 *     ->get();
 *
 * - Supports multiple words (e.g. "flav game" finds Flavius Constantin, Game Developer)
 * - Supports relationships (e.g. 'profession.name')
 * - Works with MySQL/MariaDB and PostgreSQL (auto-detect)
 * - Is case-insensitive everywhere
 */
EloquentBuilder::macro('searchIn', function (array $columns, string $search) {
    if (empty($columns) || $search === '') {
        return $this;
    }

    $driver = DB::getDriverName();
    $terms = array_filter(explode(' ', $search));

    $this->where(function (EloquentBuilder $query) use ($columns, $terms, $driver) {
        foreach ($terms as $term) {
            $term = strtolower($term);
            $query->where(function (EloquentBuilder $q) use ($columns, $term, $driver) {
                foreach ($columns as $column) {
                    if (strpos($column, '.') !== false) {
                        [$relation, $relColumn] = explode('.', $column, 2);
                        $q->orWhereHas($relation, function (EloquentBuilder $subQ) use ($relColumn, $term, $driver) {
                            if ($driver === 'pgsql') {
                                $subQ->where($relColumn, 'ILIKE', "%{$term}%");
                            } else {
                                $subQ->whereRaw('LOWER(' . $relColumn . ') LIKE ?', ["%{$term}%"]);
                            }
                        });
                    } else {
                        if ($driver === 'pgsql') {
                            $q->orWhere($column, 'ILIKE', "%{$term}%");
                        } else {
                            $q->orWhereRaw('LOWER(' . $column . ') LIKE ?', ["%{$term}%"]);
                        }
                    }
                }
            });
        }
    });

    return $this;
});