Laravel Eloquent Query Using Case/When to apply conditions dynamically

Question

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

If robot start date && robot end date are both null => return all data back
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
            }
        );
    });

Lajos Arpad · Accepted Answer · 2024-11-20 10:39:59Z

2

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

answered Nov 20, 2024 at 10:39

Lajos Arpad

80.4k42 gold badges122 silver badges234 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Yeo Bryan Over a year ago

Thanks for your suggested approach, it works as intended and I have done a little more reading in the different situations that you can apply the ``` CASE ``` condition and understand that in your suggested method it performs the condition check and only if true will it be retained in the final set of returned entries

Yeo Bryan Over a year ago

With that said, could you clarify what you mean by robots.start_date and robots.end_date not yet being ready for use yet? As earlier on in the query, I have joined the relationship to the robots table and assume that I should be able subsequently in my initial CASE condition

Lajos Arpad Over a year ago

@YeoBryan sure. The confusion stems from the fact that PHP (Eloquent) and SQL code is being mixed up in the code chunk. We need to understand that the PHP code runs first and ends up generating the SQL query and, when that query is ready, it is being executed. The when() method runs in PHP before any SQL code would run and at this point the query is not even sent to the database management system, so we cannot expect any result from there. Surely, when the generated query is being executed, which is a later point, the fields are available.

Lajos Arpad Over a year ago

@YeoBryan so the when() call appears to be inside the query code-wise, but it is happening before any query execution and from its perspective, the query is a text to be generated and to be sent to the RDBMS. Only when the query is sent to the RDBMS and it picks it up will the fields become meaningful.

Lajos Arpad Over a year ago

@YeoBryan so the flow is: 1. PHP with the help of Eloquent generates the query. You may have a call to when() at this point, but none of the fields of the query have any meaning from PHP's perspective, which basically generates a text. 2. When the query is ready, PHP sends it to the RDBMS. 3. The RDBMS picks up the query and starts executing it. At this point the fields become meaningful and can be used. 4. RDBMS sends back to PHP the results.

|

Collectives™ on Stack Overflow

Laravel Eloquent Query Using Case/When to apply conditions dynamically

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related