Laravel查询两日期区间交易时whereNotIn状态过滤失效问题排查

问题原因 #

你的查询失效是SQL逻辑运算符优先级规则导致的：SQL 中 AND 运算符优先级高于 OR，你当前编写的语句最终解析的执行逻辑等价于：

WHERE 
  (status NOT IN ('Cancelled', 'Declined', 'Finished') AND startdate BETWEEN ? AND ?)
  OR enddate BETWEEN ? AND ?
  OR (startdate >= ? AND enddate <= ?)

只要后面两个OR关联的日期条件满足，无论status是否符合过滤规则，记录都会被返回，自然whereNotIn的过滤效果就失效了。

修复方案 #

你需要把所有日期相关的判断条件都封装到同一个where闭包中，保证whereNotIn是作用于所有记录的全局过滤条件，修改后的代码如下：

$associates = Associate::join('transactions', 'associate.associate_id', '=', 'transactions.associate_id')
            ->select('associate.associate_id')
            ->whereNotIn('status', ['Cancelled', 'Declined', 'Finished'])
            // 所有日期条件封装到同一闭包，和前面的状态过滤用AND关联
            ->where(function ($query) use ($start_date, $end_date, $request) {
                $query->whereBetween('startdate',[$start_date, $end_date])
                  ->orWhereBetween('enddate',[$start_date, $end_date])
                  ->orWhere(function ($subQuery) use ($request) {
                      $start_date = new DateTime($request->input('start_date'));
                      $end_date = new DateTime($request->input('end_date'));
                      $subQuery->where('startdate','>=',$start_date)
                        ->where('enddate','<=',$end_date);
                  });
            })
            ->get();

修改后的执行逻辑变为：status符合过滤要求 AND （任意一个日期条件满足），即可同时满足日期范围查询和状态过滤的需求。

内容的提问来源于stack exchange，提问作者Kraust15