Laravel: complex query filters inside scope function

Question

I have a User model and an Availability model: User -> hasMany() -> Availabilities

The user_availability table is like

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  

Now, I want to query the user who is completely available during a certain period. For example, for the period of 2024-02-01 to 2024-02-03, the user 12345 should be returned, whereas the user 45678 should not because this user is not available on 2024-02-02.

In my UserController.php, I have

 $query->whereHas('availabilities', function ($query) use ($periodStartDate, $periodEndDate) {
     $query->fullyAvailable($periodStartDate, $periodEndDate);
 });

And inside the Availability.php model class, I tried to write a scope function:

public function scopeFullyAvailable($query, $startDate, $endDate)
{
   $query->where() // some additional filters
         ->orWhere(function ($query) use ($startDate, $endDate) {
                $startDate = Carbon::parse($startDate);
                $endDate = Carbon::parse($endDate);

                for ($date = $startDate; $date->lte($endDate); $date->addDay()) {
                     $query->whereDate('date', $date->toDateString());
                }
            })
}

But this function will return both the users because it tries to find if there is any $date that falls in the period and returns as positive. How to modify it?

Answer 1

I think it is very simple, just use whereBetween in the scope and it will filter the availabilities within the specified date range directly, and it will only return users who have availabilities for every day within that range.

The havingRaw will count of unique dates for each user within the range equals the total number of days in the range. If a user is missing any date in the range, the count won't match, and they won't be included.

public function scopeFullyAvailable($query, $startDate, $endDate) {
    $totalDays = Carbon::parse($startDate)->diffInDays(Carbon::parse($endDate)) + 1;

    $query->whereBetween('date', [$startDate, $endDate])
        ->groupBy('user_id')
        ->havingRaw('COUNT(DISTINCT date) = ?', [$totalDays]);
}