Laravel Query Builder：意外输出

Question

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!

Answer 1

Kindly take a close look at your parameter grouping https://laravel.com/docs/5.4/queries#parameter-grouping

I have restructured the query

1. Removed the and category_id != {Val} to use whereNotIn

2. Regrouped the query

   $query = Deal::where(function
            ($query){
               $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);
               });
           });
   
         if (!empty($deselectedCategories)) {
             $catList =array();
             foreach ($deselectedCategories as $key => $val) {
                 $catList[] = $val;
             }
          $query->whereNotIn('category_id', $catList);
       }
   $query->whereNull('deleted_at');
   $deals = $query->orderBy('order_date', 'desc')->orderBy('created_at', 'desc')->paginate($resultsPerPage);