Nested Query Help

First i would pull $users = User::orderByName()->get(); outside the loop. I dont see reason why it is inside. Second flip the condition if ($date->isWeekday()) to if (!$date->isWeekday()) { continue } or filter weekdays before loop. And where do you use your relations ? Is it missing in code , or i am not seeing it. If you have relations between users and covid test, you should use it when getting users. And go from there.

I'm not using the relationship... I'm confused as to how to use it to get the users that are missing the surveys on a specific date.

The date code is working the way I want it to, I just have to believe there is a better way to query this than looping through the collection of users for each date.

I see what you're saying about the user query and have moved it outside of the loop.

I was able to use collections to reduce the entire process to just two queries

public function missingSurveys($start = null, $end = null)
  {
    // If request start date is NULL get date two weeks ago.
    if(!$start) 
    {
      $start = Carbon::today()->subDays(14)->toDateString();
    }

    // If request end date is NULL use today.
    if(!$end)
    {
      $end = Carbon::today()->toDateString();
    }

    // Get all survey models between $start and $end.
    $surveys = CovidSurvey::whereBetween('attestment_date', [$start, $end])
      ->get(['user_id', 'attestment_date'])
      ->transform(function ($survey) {
        return [
          'id' => $survey->user_id,
          'date' => $survey->attestment_date,
        ];
      });  

    // Convert $surveys to collection.
    collect($surveys);
    
    // Convert $start and $end to CarbonPeriod for iteration.
    $period = CarbonPeriod::create($start, $end);

    // Create $dates collection.
    $dates = collect([]);

    // Iterate through CarbonPeriod and push to $dates collection if isWeekday.
    foreach($period as $date)
    {
      if ($date->isWeekday())
      {
        $dates->push($date);
      }
    }

    // Get all users models ordered by last_name and then first_name.
    $users = User::orderByName()
      ->get(['id', 'first_name', 'last_name', ])
      ->transform(function ($user) use ($surveys) {
        return [
          'id' => $user->id,
          'name' => $user->last_name . ', ' . $user->first_name,
          'surveys' => $surveys->where('id', $user->id)->pluck('date'),
        ];     
      });
    
    $users = collect($users);

    // Collection to hold final filtered data.
    $filtered = collect([]);
      
    foreach($dates as $date)
    {
      // Get all users that have no surveys.
      $usersWithoutSurveys = $users->filter(function ($user) {
        return $user['surveys']->count() === 0;
      });

      // Get all users that have surveys.
      $usersWithSurveys = $users->filter(function ($user) {
        return $user['surveys']->count() != 0;
      });

      // Iterate through surveys and add to $usersWithoutSurveys 
      // collection if $date does not exist in $user['surveys']
      foreach($usersWithSurveys as $user)
      {
        $userSurveys = collect($user['surveys']);

        if (!$userSurveys->contains($date->toDateString()))
          $usersWithoutSurveys->push($user);
      }

      // Re-order alphabetically. 
      $usersWithoutSurveys = $usersWithoutSurveys->sortBy(function ($user, $key) {
        return $user['name'];
      });
      
      // Reduce collection to just the users name.
      $usersWithoutSurveys = $usersWithoutSurveys->pluck('name')->flatten();

      // Push filtered data to final collection
      $filtered->push([Carbon::parse($date)->format('m/d/Y'), $usersWithoutSurveys]);
    }
    
    return Inertia::render('Reports/MissingSurveys', [
      'start' => $start,
      'end' => $end,
      'surveys' => $filtered,
    ]);
  }