Eloquent ORM: How to use multiple ‘WHERE’ clauses

Introduction

Eloquent ORM stands as one of the most prominent features of the Laravel PHP framework, providing a beautiful, simple ActiveRecord implementation for working with your database. When querying a database, the use of conditions becomes indispensable and often times you’ll find yourself needing multiple WHERE clauses to filter the dataset appropriately. In this tutorial, we will explore how to effectively use multiple WHERE clauses within Laravel’s Eloquent ORM.

Basic Usage of WHERE Clauses

Let’s start with the basics: the where method allows you to filter the results of your queries by given constraints. Here’s the general structure of a simple where query:

$users = User::where('active', 1)->get();

This query will retrieve all users where the active column is equal to 1.

Chaining Multiple WHERE Clauses

Chaining where conditions is straightforward. Let’s say we want to fetch users who are active and have the role of ‘admin’:

$admins = User::where('active', 1)->where('role', 'admin')->get();

This will produce an SQL statement similar to:

SELECT * FROM users WHERE active = 1 AND role = 'admin';

Using ‘orWhere’

Sometimes you may want to add a disjunctive OR condition to your queries. Eloquent allows this with the orWhere method:

$users = User::where('active', 1)->orWhere('role', 'admin')->get();

The resulting SQL will be:

SELECT * FROM users WHERE active = 1 OR role = 'admin';

Parameter Grouping

Parameter grouping can be critical when you have complex conditions. You might need to create a group of conditions that should be nested within parentheses. Here’s how Eloquent handles this:

$users = User::where('active', 1)
    ->where(function ($query) {
        $query->where('role', 'admin')
              ->orWhere('role', 'owner');
    })
    ->get();

The above query will group the role conditions together, which will result in the following SQL query:

SELECT * FROM users WHERE active = 1 AND (role = 'admin' OR role = 'owner');

Advanced Eloquent ‘where’ Methods

Laravel Eloquent includes more advanced conditional operators, such as whereBetween, whereIn, whereNotIn, and more. Here’s how you can use whereBetween:

$users = User::whereBetween('id', [1, 100])->get();

And an advanced example with multiple conditions:

$users = User::where('active', 1)
    ->whereIn('id', [1, 2, 3])
    ->whereNotIn('role', ['banned', 'inactive'])
    ->whereBetween('created_at', [now()->subWeek(), now()])
    ->get();

This allows for great flexibility when constructing complex queries.

Dynamic Scopes

In cases where you find yourself repeatedly writing the same complex sets of conditions, you might leverage Eloquent’s local scopes. By defining scope methods in your model, you can fluently extend your queries. Here’s an example scope in your model:

public function scopeActive($query)
{
    return $query->where('active', 1);
}

Then, in your controller you can call:

$activeUsers = User::active()->get();

Even better, you can combine scopes with other where clauses:

$activeAdmins = User::active()->where('role', 'admin')->get();

Conclusion

To navigate complex database queries, understanding and using multiple ‘WHERE’ clauses is essential. Laravel’s Eloquent provides an elegant, readable interface that simplifies these tasks. By mastering the basics and exploring advanced techniques, you can streamline your application’s data access layer to maintain clean and efficient code.