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
Recent Comments