Monday, April 10, 2017

Laravel Query Builder: Unexpected output

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.

Advertisement