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
-
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.
- The method
-
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.
- The search keyword is converted to lowercase using
-
Dynamic Field Selection:
- The trait allows for searching across multiple fields dynamically, using the
orWhereRaw
method to build the query condition.
- The trait allows for searching across multiple fields dynamically, using the
-
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.
- The
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:
-
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.
- Supports the
-
MariaDB:
- Fully supports the
LOWER
function, similar to MySQL. Case-insensitive searches can also be achieved using collation settings.
- Fully supports the
-
SQLite:
- The
LOWER
function is available in SQLite for case-insensitive searches. - By default, SQLite's
LIKE
operator is case-insensitive, but usingLOWER
is helpful for explicit case handling.
- The
-
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 theLOWER
function.
- Provides support for the
-
Oracle Database:
- Oracle also supports the
LOWER
function for performing case-insensitive searches. - Oracle offers options for setting parameters (
NLS_COMP
andNLS_SORT
) that make searches case-insensitive without explicitly usingLOWER
.
- Oracle also supports the
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.
Top comments (0)