Laravel Eloquent中Case/When动态条件查询的异常排查

问题说明 #

需求是根据robot表的start_date和end_date字段，对computer_vision_detections表应用以下过滤规则：

• 若start_date和end_date均为NULL，返回全部关联数据
• 若start_date为NULL且end_date不为NULL，返回created_at日期（忽略时间）小于等于end_date的数据

当前查询存在问题：当两个日期都为NULL时，第二个过滤条件被误触发，导致本该返回500条数据却无结果。

原因分析 #

Laravel的when()方法第一个参数若传入DB::raw()表达式，会被PHP当作非空对象判定为true，而非SQL层面的条件判断。也就是说，即使robots.end_date IS NOT NULL在SQL中不成立，第二个when()的闭包仍会执行，最终生成的SQL条件会变成：

WHERE (computer_vision_detections.id IS NOT NULL AND DATE(computer_vision_detections.created_at) <= DATE(robots.end_date))

当end_date为NULL时，DATE(robots.end_date)结果为NULL，DATE(...) <= NULL在SQL中不会匹配任何行，导致返回空数据。

修复方案 #

方案1：利用已获取的Robot实例属性判断（推荐） #

先获取Robot实例，直接用PHP变量做条件判断，避免重复关联robots表，逻辑更清晰：

$robot = App\Robot::find(9434);

$robot->load([
    'computer_vision_detections' => function ($query) use ($robot) {
        $query->leftJoin('cv_detection_object_values', function ($join) {
            $join->on('computer_vision_detections.detection_object_id', '=', 'cv_detection_object_values.id')
                 ->whereColumn('computer_vision_detections.detection_type_id', '=', 'cv_detection_object_values.detection_type_id');
        })
        ->select(
            'computer_vision_detections.*',
            'cv_detection_object_values.detection_type_id AS cv_detection_object_values_detection_type_id',
            'cv_detection_object_values.id AS cv_detection_object_values_id_detection_object_id',
            'cv_detection_object_values.description AS cv_detection_object_values_description'
        )
        // 两个日期都为空时，无需额外过滤
        ->when(is_null($robot->start_date) && is_null($robot->end_date), function ($q) {})
        // start空、end非空时应用日期过滤
        ->when(is_null($robot->start_date) && !is_null($robot->end_date), function ($q) use ($robot) {
            $q->whereRaw('DATE(computer_vision_detections.created_at) <= DATE(?)', [$robot->end_date]);
        });
    },
    'computer_vision_detections.detection_type'
]);

方案2：用原生SQL条件分支组合逻辑 #

如果必须在关联查询内处理，可直接用SQL的OR/AND组合条件，替代when()的判断：

$robot = App\Robot::with([
    'computer_vision_detections' => function ($query) {
        $query->leftJoin('cv_detection_object_values', function ($join) {
            $join->on('computer_vision_detections.detection_object_id', '=', 'cv_detection_object_values.id')
                 ->whereColumn('computer_vision_detections.detection_type_id', '=', 'cv_detection_object_values.detection_type_id');
        })
        ->leftJoin('robots', 'computer_vision_detections.serial_number', '=', 'robots.serial_number')
        ->select(
            'computer_vision_detections.*',
            'cv_detection_object_values.detection_type_id AS cv_detection_object_values_detection_type_id',
            'cv_detection_object_values.id AS cv_detection_object_values_id_detection_object_id',
            'cv_detection_object_values.description AS cv_detection_object_values_description'
        )
        ->where(function ($query) {
            // 满足以下任一条件：1. 两个日期都为空；2. start空且end非空，同时created_at<=end_date
            $query->whereRaw('robots.start_date IS NULL AND robots.end_date IS NULL')
                  ->orWhere(function ($q) {
                      $q->whereRaw('robots.start_date IS NULL AND robots.end_date IS NOT NULL')
                        ->whereRaw('DATE(computer_vision_detections.created_at) <= DATE(robots.end_date)');
                  });
        });
    },
    'computer_vision_detections.detection_type'
])->find(9434);

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