Laravel Eloquent: Using ‘LIKE’ and ‘NOT LIKE’ operators

Introduction

Laravel’s Eloquent ORM is a powerful tool that makes database operations a breeze. When it comes to building queries, there are times when we need to implement search functionality or simply filter results based on patterns. This is where the SQL ‘LIKE’ and ‘NOT LIKE’ operators come into play. In this tutorial, you’ll learn how to use these operators within Laravel’s Eloquent to craft effective queries.

Before we dive in, ensure you have a Laravel project set up with a working database connection. We’ll assume you have the necessary Models for your database tables ready.

Understanding ‘LIKE’ and ‘NOT LIKE’

The ‘LIKE’ operator in SQL is used to search for a specified pattern in a column. For instance, here’s a simple SQL query using ‘LIKE’:

SELECT * FROM users WHERE name LIKE '%doe%';

In Eloquent, the equivalent would be:

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

The ‘%’ sign is a wildcard, which means ‘any sequence of characters’. So the query above selects all users whose name contains ‘doe’.

Likewise, ‘NOT LIKE’ is used to exclude records with a certain pattern. In plain SQL, it would look like this:

SELECT * FROM users WHERE name NOT LIKE '%doe%';

In Eloquent, you use ‘NOT LIKE’ similarly:

$users = User::where('name', 'NOT LIKE', '%doe%')->get();

Case Sensitivity

By default, ‘LIKE’ queries are case-insensitive when using MySQL or PostgreSQL. With SQLite or SQL Server, ‘LIKE’ operations are case-sensitive. You must consider this when your application could potentially run on multiple databases.

Basic Usage of ‘LIKE’

Let’s assume you are building a user management system and need to fetch users with ‘jo’ in their names. In Eloquent, you would write:

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

This will return a collection of User models where the ‘name’ column contains ‘jo’.

Searching with ‘NOT LIKE’

Now consider a scenario where you want to exclude all users with ‘jo’ in their names. The Eloquent approach would be:

$users = User::where('name', 'NOT LIKE', '%jo%')->get();

This will fetch all users whose names don’t have ‘jo’ in them.

Combining Conditions

Eloquent allows you to chain multiple ‘where’ clauses to create more specific queries. For example, you might want users named ‘john’ who do not have ‘doe’ in their email:

$users = User::where('name', 'LIKE', '%john%')
                ->where('email', 'NOT LIKE', '%doe%')
                ->get();

In this case, Eloquent will combine the conditions with an ‘AND’ operator, resulting in a query that targets users named ‘john’ and filters out any with ‘doe’ in their email addresses.

Using ‘orWhere’ with ‘LIKE’

What if you want to broaden the search to include users named ‘john’ or ‘jane’? Here’s how you can utilize the ‘orWhere’ clause:

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

This query will return users with either ‘john’ or ‘jane’ in their names.

LIKE with Relationships

You can also perform ‘LIKE’ queries on related models. If users have posts and we want to find users with posts that contain a certain keyword, one way to achieve this is:

$users = User::whereHas('posts', function ($query) {
    $query->where('content', 'LIKE', '%keyword%');
})->get();

This query filters the users based on the content of their posts.

Tips for Using Wildcards

Wildcards are powerful. ‘%keyword%’ will find any record containing the term ‘keyword’ anywhere. ‘keyword%’ will match anything that starts with ‘keyword’, and ‘%keyword’ matches anything that ends with ‘keyword’. As such, you must decide where you need the flexibility and where you need specificity. Also, keep in mind that leading wildcards can negatively impact performance due to full table/index scans.

Security: Preventing SQL Injection

It’s crucial to prevent user input from interacting directly with your database queries. Laravel’s Eloquent handles this by preparing statements that make use of parameter binding. Here’s a safe way to handle user inputs:

$searchTerm = request('name');
$users = User::where('name', 'LIKE', "%{$searchTerm}%")->get();

This ensures the search term is safely bound to the query, thereby preventing potential SQL injection.

Case Sensitivity Caveats

If you’re dealing with a case-sensitive database and need a case-insensitive search, you may need to use a raw query with the appropriate SQL functions. For databases like PostgreSQL, you have the ILIKE operator as an alternative:

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

Remember that raw expressions should be used sparingly and with caution, as they can expose your application to SQL injection if not handled correctly.

Conclusion

‘LIKE’ and ‘NOT LIKE’ are indispensable tools in the repertoire of anyone working with databases in Laravel. They provide great flexibility in querying data sets, enabling you to retrieve the exact subset of data you need. By following best practices and combining them with other Eloquent methods, you can build secure, efficient, and expressive queries to handle even the most complex search criteria.

As we wrap up this tutorial, remember the impact your queries can have on database performance and ensure that user inputs are always sanitized. With these points in mind, you’re now equipped to use ‘LIKE’ and ‘NOT LIKE’ within Laravel’s Eloquent ORM to their full potential.