whereBetween not working as expected when chaining in Laravel

Question

I have a user_orders table and an User -> hasMany() -> Orders:

id  | user_id |      date 
123 |  12345  |   2024-02-01   
124 |  12345  |   2024-02-02
125 |  12345  |   2024-02-03
126 |  45678  |   2024-02-01 
127 |  45678  |   2024-02-03  

I want to find the user who has the order for every day between $startDate and $endDate. For example, when $startDate = 2024-02-01 and $endDate = 2024-02-03 only the user_id = 12345 should be returned, whereas the user_id = 45678 shouldn't because this user does not have a order for 2024-02-02. My question is, why is whereBetween not working anymore if placed inside the chaining function?

// not working as both user 12345 and 45678 are returned
User::whereHas('Orders', function ($query) use ($startDate, $endDate) {
        $query->where('some conditions')
              ->orWhere(function($query) use ($startDate, $endDate) {
                   $query->whereBetween('date', [$startDate, $endDate])
                         ->groupBy('user_id')
                         ->havingRaw('COUNT(DISTINCT date) = ? ', [diffInDays($startDate, $endDate)]) 
              });
}

// working, correctly returns only user 12345
User::whereHas('Orders', function ($query) use ($startDate, $endDate) {
         $query->where('some conditions')
               ->orWhereBetween('date', [$startDate, $endDate])
               ->groupBy('user_id')
                ->havingRaw('COUNT(DISTINCT date) = ? ', [diffInDays($startDate, $endDate)]) 
}

Answer 1

In the First Query, The groupBy and havingRaw clauses are inside the orWhere closure and will only be applied when the orWhere condition is met, which might not be the case for all relevant rows.

Now, lets take the example for user_id = 45678 by looking into the following conditions as you commented for the example.

some conditions are false, whereBetween is true:

In this case, groupBy and havingRaw will be applied. But since user_id = 45678 doesn’t have an order for every day in the range, it should not be included. However, because some conditions failed, this user might still be included.

Both some conditions and whereBetween are true:

In this scenario, the user might be included correctly based on the groupBy and havingRaw conditions.

some conditions are true, whereBetween is false:

The user will be included based on the some conditions regardless of the whereBetween clause.

The Second Query is working because that the whereBetween is directly chained with the where clause, and the groupBy and havingRaw clauses are applied to the whole query, not conditionally. This makes the query more straightforward and ensures that the grouping and having clauses are always applied.

The first Query should be written like this,

User::whereHas('Orders', function ($query) use ($startDate, $endDate) {
    $query->where(function($query) use ($startDate, $endDate) {
       $query->where('some conditions')
            ->orWhereBetween('date', [$startDate, $endDate]);
    })
    ->groupBy('user_id')
    ->havingRaw('COUNT(DISTINCT date) = ? ', [diffInDays($startDate, $endDate)]);
});