When building applications, especially those with search functionalities, making the search case-insensitive is often crucial to improving user experience. The Searchable trait in Laravel simplifies this process, allowing developers to implement a case-insensitive search across one or multiple fields of a model. In this blog post, we'll explore how this trait works, how you can incorporate it into your Laravel projects, and which databases support the necessary LOWER function.

The Searchable Trait Explained

The Searchable trait is designed to extend Laravel's Eloquent query builder with a method that enables case-insensitive search across specified fields. This means that whether a user types "John" or "john," the results will include records that match either capitalization.

Here’s a breakdown of the key components in the Searchable trait:

namespace App\Concerns\Scopes;

use Illuminate\Database\Eloquent\Builder;
use Illuminate\Support\Arr;

trait Searchable
{
    /**
     * Apply a case-insensitive search to a query builder.
     *
     * @param  string|array  $field
     * @param  string  $keyword
     * @return Builder
     */
    public static function scopeSearch(Builder $query, string|array $fields, $keyword)
    {
        $keyword = strtolower($keyword);

        if (is_string($fields)) {
            $fields = Arr::wrap($fields);
        }

        foreach ($fields as $field) {
            $field = '"'.str_replace('.', '"."', $field).'"';
            $query->orWhereRaw('LOWER('.$field.') LIKE ?', ['%'.$keyword.'%']);
        }

        return $query;
    }
}

How It Works

1. Accepting Multiple Fields:

   • The method scopeSearch accepts a string or an array of fields to search in. If a single field is passed as a string, it's wrapped into an array for consistency.

2. Case-Insensitive Search:

   • The search keyword is converted to lowercase using strtolower, ensuring that the search is case-insensitive.
   • The fields are also converted to lowercase in the raw SQL query to match the lowercase keyword.

3. Dynamic Field Selection:

   • The trait allows for searching across multiple fields dynamically, using the orWhereRaw method to build the query condition.

4. Support for Nested Fields:

   • The str_replace function is used to handle nested fields (e.g., profile.first_name), converting them to a format suitable for raw SQL queries.

Using the Searchable Trait in Your Models

To use this trait in a Laravel model, you simply need to import it into your model and use the search method in your queries.

use App\Concerns\Scopes\Searchable;

class User extends Model
{
    use Searchable;
}

Now, you can perform a search on the User model like so:

$users = User::search(['name', 'email'], 'john')->get();

This will return all users whose name or email fields contain the string "john" in a case-insensitive manner.

Database Support for the LOWER Function

The LOWER function used in the trait is supported by most popular relational database systems, making it a versatile solution for your Laravel applications. Here are some of the databases that support the LOWER function:

1. MySQL:

   • Supports the LOWER function to convert text to lowercase for case-insensitive searches.
   • MySQL also allows you to use case-insensitive collations (e.g., utf8_general_ci), making searches case-insensitive by default.

2. MariaDB:

   • Fully supports the LOWER function, similar to MySQL. Case-insensitive searches can also be achieved using collation settings.

3. SQLite:

   • The LOWER function is available in SQLite for case-insensitive searches.
   • By default, SQLite's LIKE operator is case-insensitive, but using LOWER is helpful for explicit case handling.

4. SQL Server (Microsoft SQL Server):

   • Provides support for the LOWER function.
   • SQL Server's default collation (SQL_Latin1_General_CP1_CI_AS) is case-insensitive, so searches are often case-insensitive without needing the LOWER function.

5. Oracle Database:

   • Oracle also supports the LOWER function for performing case-insensitive searches.
   • Oracle offers options for setting parameters (NLS_COMP and NLS_SORT) that make searches case-insensitive without explicitly using LOWER.

Conclusion

The Searchable trait is a powerful tool to enhance your Laravel models with case-insensitive search capabilities. With support from all major relational databases — including MySQL, MariaDB, SQLite, SQL Server, and Oracle — this trait provides a consistent way to implement case-insensitive search functionality. By allowing flexible field selection and handling case conversion under the hood, it simplifies the development of robust and user-friendly search features in your Laravel applications.