Laravel Eloquent Query Using Case/When to apply conditions dynamically

I am trying to apply a different set of date filter depending on the values of the start_date and end_date column of the robot table

Condition

1. If robot start date && robot end date are both null => return all data back
2. Else if the robot's start date is NULL and the end date is not NULL, return all data where the created_at date of computer_vision_detections is less than or equal to the robot's end_date (only comparing the date, ignoring the time).

Issue

There is an issue with how the WHEN portion of the query is applied such that despite both the start_date and end_date column being NULL it is still entering the second WHEN query causing no data to be returned when there is supposed to be 500 data entries returned

Code

    $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) {
                $query->when(
                    DB::raw('robots.start_date IS NULL AND robots.end_date IS NULL'),
                    function ($q) {
                        $q->whereNotNull('computer_vision_detections.id'); // Take all data if both start and end are null
                    }
                )
                ->when(
                    DB::raw('robots.start_date IS NULL AND robots.end_date IS NOT NULL'),
                    function ($q) {
                        $q->whereRaw('DATE(computer_vision_detections.created_at) <= DATE(robots.end_date)'); // All data before or equals to end date
                    }
                );
            });
        },
        'computer_vision_detections.detection_type' // This assumes the relationship is defined properly
    ])
    ->find(9434);

Debugging

When the WHEN query is just reduced to this one condition, it is able to return the 500 data entries which indicates to me that there is no issue with this portion of the query and that the issue is due to the second WHEN query also being executed despite the end_time being NULL hence resulting in the entries returned being 0

    ->where(function ($query) {
        $query->when(
            DB::raw('robots.start_date IS NULL AND robots.end_date IS NULL'),
            function ($q) {
                $q->whereNotNull('computer_vision_detections.id'); // Take all data if both start and end are null
            }
        );
    });

If you take a look at [conditional clauses][1] in the documentation, you will see this statement:

Sometimes you may want certain query clauses to apply to a query based on another condition.

And this example:

$role = $request->input('role');
 
$users = DB::table('users')
                ->when($role, function (Builder $query, string $role) {
                    $query->where('role_id', $role);
                })
                ->get();

You have a first parameter here, called $role and if it's true, then the where is added to the query.

However, what you expect from the when is to conditionally add query parts based on the partial results of the query. However, this is not how it works. First, you write (or generate in this case) your query, then you execute it and only then you will find out what the results are. You do not have the results of your query yet when it is being constructed, so robots.start_date IS NULL AND robots.end_date IS NOT NULL will not meaningfully be evaluated and, what you get instead is whether DB::raw('some text here') is truey.

DB::raw returns a \Illuminate\Database\Query\Expression which, if not null, will always be truey and it will not be meaningfully equivalent to the check you wanted.

Instead of your current approach, you could have something like this:

->whereRaw('
    CASE 
        WHEN robots.start_date IS NULL AND robots.end_date IS NOT NULL THEN computer_vision_detections.id IS NOT NULL
        WHEN robots.start_date IS NULL AND robots.end_date IS NOT NULL THEN DATE(computer_vision_detections.created_at) <= DATE(robots.end_date)
        ELSE TRUE
    END
')

And this encapsulated your first case in the first WHEN, so if that's met, then the other field is checked not to be null, the second case with your date comparison and falls back to TRUE if neither of them were correct. [1]: https://laravel.com/docs/11.x/queries#conditional-clauses