How To Use Multiple Where Clauses In Laravel Eloquent?

Using Array-Based Where Conditions

Laravel lets you handle multiple where conditions using an array-based approach. This method lets you pass an array of conditions to the where method, making your code easier to read and maintain.

The syntax for using where with an array is:

$results = User::where([
    ['column1', '=', 'value1'],
    ['column2', '=', 'value2'],
    ['column3', '=', 'value3']
])->get();

Each inner array represents a single where condition, with the format [column, operator, value]. This approach is useful when you have many conditions that all use the same logical operator (AND).

$results = User::where([
    ['status', '=', 'active'],
    ['age', '>', 18]
])->orderBy('created_at', 'desc')
  ->limit(10)
  ->get();

Using the whereIn Method for Multiple Values

The whereIn method is useful when you need to check if a column's value matches any value in a given array. This is helpful for filtering based on multiple possible values for a single column.

Here's how you can use whereIn:

$results = User::whereIn('status', ['active', 'pending', 'suspended'])->get();

This query will return all users where the status is 'active', 'pending', or 'suspended'.

You can also use whereIn with multiple columns:

$results = User::whereIn('id', [1, 2, 3])
               ->whereIn('role', ['admin', 'moderator'])
               ->get();

This query will return users with ids 1, 2, or 3 who also have the role of 'admin' or 'moderator'.

The whereIn method is useful when you have a list of values to check against, such as when filtering by multiple categories or tags.

Using Closure-Based Where Clauses

Closure-based where clauses in Laravel Eloquent let you create complex conditions. You can group multiple conditions and apply advanced logic to your queries.

Here's an example:

$results = User::where(function($query) {
    $query->where('status', 'active')
          ->where('age', '>', 18);
})->get();

This query returns all active users over 18 years old. The closure groups these conditions together.

You can also nest closures for more complex logic:

$results = User::where(function($query) {
    $query->where('status', 'active')
          ->orWhere(function($query) {
              $query->where('status', 'pending')
                    ->where('created_at', '>', now()->subDays(7));
          });
})->get();

This query returns all active users, or pending users who registered in the last 7 days.

$results = User::where(function($activeOrRecentlyPendingQuery) {
    $activeOrRecentlyPendingQuery->where('status', 'active')
        ->orWhere(function($recentlyPendingQuery) {
            $recentlyPendingQuery->where('status', 'pending')
                ->where('created_at', '>', now()->subDays(7));
        });
})->get();

Combining Multiple Where Methods

Laravel Eloquent lets you mix different where methods to create powerful queries. Here are some examples:

Using orWhere:

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

This query returns users who are either active or have an admin role.

Using whereBetween:

$results = User::whereBetween('age', [18, 65])
               ->where('status', 'active')
               ->get();

This query returns active users between 18 and 65 years old.

Using whereNotNull:

$results = User::where('status', 'active')
               ->whereNotNull('email_verified_at')
               ->get();

This query returns active users who have verified their email address.

By combining these methods, you can create complex queries that filter your data as needed.

Using Raw SQL Within Eloquent

Eloquent offers many query building methods, but sometimes you need to use raw SQL for complex queries or to improve performance. Laravel lets you use raw SQL within Eloquent queries.

You can use the whereRaw method to add raw where clauses:

$results = User::whereRaw('age > ? and status = ?', [18, 'active'])->get();

For more complex raw SQL, you can use the selectRaw method:

$results = User::selectRaw('count(*) as user_count, status')
               ->groupBy('status')
               ->get();

When you need to run a custom query, you can use the DB facade:

$results = DB::select('SELECT * FROM users WHERE status = ?', ['active']);

Using Eloquent Relationships for Complex Queries

Eloquent relationships can simplify complex queries involving multiple tables. You can use the with method for eager loading related models:

$users = User::with('posts')->get();

This query loads all users and their associated posts in two database queries.

The whereHas method allows you to filter the main query based on related models:

$users = User::whereHas('posts', function($query) {
    $query->where('published', true);
})->get();

This query retrieves all users who have at least one published post.

You can combine these methods for more complex queries:

$users = User::with(['posts' => function($query) {
    $query->where('published', true);
}])->whereHas('comments', function($query) {
    $query->where('approved', true);
})->get();

This query loads all users who have approved comments, along with their published posts.

By using these relationship methods, you can write clean, readable code that handles complex database operations well.

$latestPosts = Post::addSelect(['latest_comment_date' => 
    Comment::select('created_at')
        ->whereColumn('post_id', 'posts.id')
        ->latest()
        ->limit(1)
])->get();