AND-OR-AND + brackets with Eloquent-Laravel 5.1

AND-OR-AND + brackets with Eloquent-Laravel 5.1:

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.

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) use ($gender, $age) {
    $query->where('gender', $gender)
        ->where('age', '>=',  $age;
})->orWhere(function($query) use ($gender, $age) {
    $query->where('gender',  $gender)
        ->where('age', '>=',  $age);
})
This code will produce SQL query exactly like we need – with brackets in the right places.

Thanks.