Laravel – AND-OR-AND + Brackets With Eloquent

Eloquent is a great thing where you can build your query step-by-step and then call get() method. But sometimes it gets a little tricky for more complicated queries. 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

Example we need to filter male customers aged 18+ or female customers aged 65+. Simple MySQL query would look something like this:

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

Now, transform it to Eloquent:

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

But 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 the wrong order where 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. If you don’t test properly, you wouldn’t even notice that it filtered out wrong results.

Right way – putting “brackets” into Eloquent

// ...
$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.

Credit to: https://bit.ly/3uNaSHp