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) {
$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.
Thank you Povilas, it’s very clear now, they mention it as constraints that contained within parenthesis in Laravel documents and they call it “Parameter Grouping”.
can you tell me how can i write this query?? it’s showing unexpected ‘,’
$users=DB::table(“bookings”)
->select(“id”)
->where([
[‘bookdate’, ‘=’, $request->input(‘bookdate’)],
[‘roomname’, ‘=’, $request->input(‘roomname’)],
[($dbsum,’>’,’c_start’ OR $dbsum, ”,’c_start’ OR $dbesum,’get();
This seems wrong…
… WHERE ((gender = ‘Male’ and age >= 18) or gender = ‘Female’) and age >= 65
above returns male or female above 64, the 18 becomes useless because you are stating AND age is above 65 outside of the nest. all this is doing is blocking the query from considering any male under 19, which it does anyway.
For this to work the laravel code would look like…
$query->where(function ($query) {
$query->where(function ($query) {
$query->where(‘gender’, ‘Male’)
->where(‘age’, ‘>=’, ’18’);
})->orWhere(function ($query) {
$query->where(‘gender’, ‘Female’)
->where(‘age’, ‘>=’, ’65’);
});
});
creating…
… WHERE ((gender = ‘Male’ AND age >= 18) OR (gender = ‘Female’ AND age >= 65)) is the correct format.
Hello, thanks for the help but still, how do i pass in variables so as to use them in the where clause inside that anonymous function
$q->where( function( $query ){
$query->where( ‘gender’, $gender->gender_one )->where( ‘age’, $age->age_one );
})orWhere( function( $query ){
$query->where(‘gender’, $gender->gender_two)->where(‘age’, $age->age_two);
})->get();
$q->where(function($query) use($gender, $age) {
…
})->get();
Povilas, you are a genius!!!
thanks a lot! I fixed my code and now I can go to bed!!!
Correct, but let’s be 100% honest… it’s ugly! Laravel’s syntax is supposed to be sexy and clean, and in most instances it is, but here I much prefer raw SQL!
I totally agree with you. The Query Builder is great, but this is one of the few ugly things, as complex where clauses get transformed into multiple nested closures, each one with a different param name for “$query” and, if you need to pass any other variable, you have to pass it with “use”, which makes the whole query much more verbose and incomprehensible.
I’m trying to figure out if there is any other way to write it without using raw queries.
I love U,
U save my day.
Concise and useful – perfect!
Thank you
Thank you Povilas,
But the query were be executed in this order actually in your example:
…. WHERE ((gender = ‘Male’ and age >= 18) or gender = ‘Female’) and age >= 65
I see the And has precedence over Or, so I think the query actually will be
… WHERE (gender = ‘Male’ and age >= 18) or (gender = ‘Female’ and age >= 65)
I tried with own db with order as same as with you (… and … or … and …) and I got the result with the order I told above:
http://prntscr.com/ih8nkb
Pleas check this i have a query
$mrf= DB::table(‘tablename’)->where(‘review’, ‘yes’)
->Where(‘company_department’, ‘it’)
->orWhere(‘company_department’, ‘admin’)
->orWhere(‘company_department’, ‘qc’)
->orWhere(‘company_department’, ‘projects’)
->orderBy(‘created_at’, ‘desc’)->tosql();
the DD statement output is like this
“select * from `items` where `review` = ? and `company_department` = ? or `company_department` = ? or `company_department` = ? or `company_department` = ? order by `created_at` desc
but how can i transform above eloquent version so that i can get below version. the below has bracket after first AND
and then inside OR that i need
“select * from `items` where `review` = ? and ( `company_department` = ? or `company_department` = ? or `company_department` = ? or `company_department` = ? ) order by `created_at` desc
you saved my mind 😀
Hello, I think there’s a typo.
This:
$q->where(‘gender’, ‘Male’);
$q->orWhere(‘age’, ‘>=’, 18);
$q->where(‘gender’, ‘Female’);
$q->orWhere(‘age’, ‘>=’, 65);
Actually converts to this sql query:
WHERE gender = “Male” or age >= 18 and gender = ‘Female’ or age >= 65
In the article it is:
WHERE gender = ‘Male’ and age >= 18 or gender = ‘Female’ and age >= 65