For the last two hours I'm struggling with a MySQL query that is created using the Laravel Query Builder, but I just don't understand why a parameter seems to be ignored.
The query that comes out of the query builder is the following:
select * from `deals` where
(
(
`created_at` <= '2017-04-10 09:44:07'
and `valid_until` >= '2017-04-10 09:44:07'
) or (
`created_at` <= '2017-04-10 09:44:07'
and `valid_until` <= '2017-04-10 09:44:07'
and `autodelete` = '2'
) or (
`created_at` <= '2017-04-10 09:44:07'
and `valid_until` <= '2017-04-10 09:44:07'
and `autodelete` = '0'
) or (
`created_at` <= '2017-04-10 09:44:07'
and `valid_until` is null
)
and `category_id` != '1'
) and `deals`.`deleted_at` is null
order by `order_date` desc, `created_at` desc
limit 20
offset 0
The part that is being ignored is the filtering of the category_id. Items that have the category_id 1 should be excluded, but they won't. And I don't understand why.
Now I found out that when I move that part to outside the parentheses, it will work. The only problem is: I'm not adding those parentheses to the query, it seems that Laravel is doing that automatically because I'm using soft deletes.
This is the code I'm using for creating the query:
$query = Deal::query()
->where(function ($query) {
// Active with an end date
$query->where('created_at', '<=', date("Y-m-d H:i:s"))
->where('valid_until', '>=', date("Y-m-d H:i:s"));
})
->orwhere(function ($query) {
// Ended, but with possibility to upload a receipt
$query->where('created_at', '<=', date("Y-m-d H:i:s"))
->where('valid_until', '<=', date("Y-m-d H:i:s"))
->where('autodelete', 2);
})
->orwhere(function ($query) {
// Ended, but keep it online
$query->where('created_at', '<=', date("Y-m-d H:i:s"))
->where('valid_until', '<=', date("Y-m-d H:i:s"))
->where('autodelete', 0);
})
->orwhere(function ($query) {
// No end date
$query->where('created_at', '<=', date("Y-m-d H:i:s"))
->where('valid_until', null);
});
// Filter categories
if(!empty($deselectedCategories))
{
foreach($deselectedCategories as $key => $val)
{
$query->where('category_id', '!=', $val);
}
}
$deals = $query->orderBy('order_date', 'desc')->orderBy('created_at', 'desc')->paginate($resultsPerPage);
I must be overlooking something and I hope someone can help me out. Thank you very much in advance!
via Rick L.