AND-OR-AND + brackets with Eloquent

Eloquent is a great thing - you can build your query step-by-step and then call get() method. But sometimes it gets a little tricky for more complicated queries - for example, if you have multiple AND-OR conditions and you want to put brackets, how to do it properly?

Wrong way - easy to make mistake

Let's say we need to filter male customers aged 18+ or female customers aged 65+ (whatever the reason is, it's just a hypothetical example). Simple MySQL query would look something like this:

... WHERE (gender = 'Male' and age >= 18) or (gender = 'Female' and age >= 65)

Now let's transform it to Eloquent:

// ...
$q->where('gender', 'Male');
$q->orWhere('age', '>=', 18);
$q->where('gender', 'Female');
$q->orWhere('age', '>=', 65);

But wait, if we launch it like that, MySQL query wouldn't have any brackets and would be launches as this:

... WHERE gender = 'Male' and age >= 18 or gender = 'Female' and age >= 65

Which is wrong order - it would actually be executed in this order:

... WHERE ((gender = 'Male' and age >= 18) or gender = 'Female') and age >= 65

The worst thing is that it wouldn't throw any errors. And if you don't test properly, you wouldn't even notice that it filtered out wrong results.

Want more articles like this every week? Subscribe!

Right way - putting "brackets" into Eloquent

What we actually need here is a thing called Advanced Where Clauses - where we can assign a function to a where clause, like this:

// ...
$q->where(function ($query) {
    $query->where('gender', 'Male')
        ->where('age', '>=', 18);
})->orWhere(function($query) {
    $query->where('gender', 'Female')
        ->where('age', '>=', 65);
})

This code will produce SQL query exactly like we need - with brackets in the right places.

AND-OR-AND + brackets with Eloquent

Wrong way - easy to make mistake

Right way - putting "brackets" into Eloquent

Like our articles?

Recent New Courses

Roles and Permissions in Laravel 11

[Mini-Course] Laravel 11: Breeze with User Role Areas

Structuring Databases in Laravel 11