Laravel Query Builder: Search Results with ‘LIKE’ Operator

Introduction

Laravel, the popular PHP framework for web artisans, simplifies database operations by providing an elegant, fluent interface for creating and running database queries. One commonly used database operation is searching, often facilitated by the SQL ‘LIKE’ operator. This tutorial will guide you through the use of the ‘LIKE’ operator within Laravel’s Query Builder to perform search queries.

Getting Started with LIKE Queries

The ‘LIKE’ operator in SQL is used to search for a specified pattern in a column. In Laravel’s Query Builder, the basic ‘like’ query looks something like this:

$users = DB::table('users')
    ->where('name', 'LIKE', '%searchTerm%')
    ->get();

This will retrieve all users where the ‘name’ column contains ‘searchTerm’. Note the use of percentage signs (%) as wildcards, which indicate that the pattern may be present anywhere within the string.

Case Insensitive Searching

To perform a case insensitive search, most databases support ‘ILIKE’. However, this is not standard SQL and may not be supported by your database:

$users = DB::table('users')
    ->where('name', 'ILIKE', '%searchTerm%')
    ->get();

If your database does not support ‘ILIKE’, you can use ‘like’ in conjunction with ‘LOWER()’ to achieve case insensitive searching:

$users = DB::table('users')
    ->whereRaw('LOWER(name) LIKE ?', [strtolower('%searchTerm%')])
    ->get();

Advanced LIKE Queries

You may want to perform a more complex search that involves multiple columns. Using Query Builder, you can chain multiple ‘where’ clauses:

$users = DB::table('users')
    ->where('name', 'LIKE', '%searchTerm%')
    ->orWhere('email', 'LIKE', '%searchTerm%')
    ->get();

This retrieves all users where either their name or email contains ‘searchTerm’.

Search with Joins

You can also join tables and perform ‘LIKE’ queries across them:

$users = DB::table('users')
    ->join('profiles', 'users.id', '=', 'profiles.user_id')
    ->where('users.name', 'LIKE', '%searchTerm%')
    ->orWhere('profiles.bio', 'LIKE', '%searchTerm%')
    ->get();

This query will return users based on a search term present either in their name or their bio, which is in a separate ‘profiles’ table but related by the user’s id.

Using LIKE with Laravel Eloquent

Laravel’s Eloquent ORM provides an even more fluent and expressive way of working with your database. Eloquent models allow for similar ‘like’ queries:

$users = User::where('name', 'LIKE', '%searchTerm%')
    ->orWhere('email', 'LIKE', '%searchTerm%')
    ->get();

This achieves the same result as the prior examples but utilizes the Eloquent User model.

Dynamic LIKE Queries

Sometimes you’ll need to construct a query based on variable user input. Here’s how you might integrate user input while protecting against SQL injection:

$searchTerm = Request::input('search');
$users = DB::table('users')
    ->where('name', 'LIKE', "%{$searchTerm}%")
    ->get();

This code takes search input from the user and includes it safely within a ‘LIKE’ query.

LIKE with Pagination

Laravel also makes it possible to paginate the results from a ‘LIKE’ query to avoid returning too many results at once:

$users = User::where('name', 'LIKE', '%searchTerm%')
    ->orWhere('email', 'LIKE', '%searchTerm%')
    ->paginate(15);

This will paginate the search results, displaying 15 users per page, while still adhering to the ‘LIKE’ query constraints.

Conclusion

Throughout this tutorial, we’ve explored various ways to implement search functionality using the ‘LIKE’ operator in Laravel’s Query Builder. By applying these techniques, you can create powerful, flexible search features in your Laravel applications. Always remember to validate and sanitize user input to protect against SQL injection and maintain a secure application.