Laravel 8 database query – How do I build this collection of related data? – PHP

I am trying to figure out how to form my database query to build up a collection of projects, each of which has a set of personnel with their assigned working periods and the daily shift hours that they submit.

In the code I have so far, I select the projects that are running for the current month. For each of those I select the personnel that are working on the project during that month. Then for each of the personnel I make another 2 queries to get their assignment dates and submitted shift details.

This does work, and creates the data structure I want, but it’s slow due to the large number of individual database queries.

There must be a more efficient way to do this through subqueries or something?

$projects = Project::select([
    'projects.id',
    'projects.name',
    'projects.start',
    'projects.finish'
])
->where('projects.start', '<=', Carbon::now()->endOfMonth())
->where('projects.finish', '>=', Carbon::now()->startOfMonth())
->get();

foreach($projects as $project)
{
    $project->personnel = ProjectUser::select([
        'project_users.id',
        'project_users.user_id',
        'users.name',
        'project_users.role'
    ])
    ->join('users', 'users.id', '=', 'project_users.user_id')
    ->join('assignments', 'assignments.project_user_id', '=', 'project_users.id')
    ->where('project_users.project_id', $project->id)
    ->where('assignments.start', '<=', Carbon::now()->endOfMonth())
    ->where('assignments.finish', '>=', Carbon::now()->startOfMonth())
    ->distinct('users.name')
    ->get();

    foreach($project->personnel as $person)
    {
        $person->assignments = Assignment::select([
            'assignments.start',
            'assignments.finish',
            'assignments.travel_out',
            'assignments.travel_home'
        ])
        ->where('assignments.project_user_id', $person->project_user_id)
        ->get();

        $person->shifts = WorkShift::select([
            'work_shifts.id',
            'work_shifts.role',
            'work_shifts.date',
            'work_shifts.start',
            'work_shifts.hours',
            'work_shifts.status',
            'work_shifts.remarks'
        ])
        ->where('work_shifts.user_id', $person->user_id)
        ->where('work_shifts.project_id', $project->id)
        ->get();
    }
}

Update

I now have this mostly working through the use of model relationships. My last problem is that I can filter the projects within a date range at the top level in my controller, but I also need to apply that filter to the assignments and work_shifts.

So, I only want to retrieve project_users that have assignments within the requested date range and then pick out only the assignments and work_shifts that match.

What I have so far is…

// Controller
public function load(Request $request)
{
  $projects = Project::select([
    Project::ID,
    Project::NAME,
    Project::START,
    Project::FINISH
  ])
    // Only able to filter by date range here?!?
    ->where(Project::START, '<=', Carbon::now()->endOfYear())
    ->where(Project::FINISH, '>=', Carbon::now()->startOfYear())
    ->with('project_staff')
    ->orderBy(Project::START)
    ->get();

  return response()->json([
    'projects' => $projects
  ]);
}

// Project Model
// Just want to get the staff that are assigned to the project
// between the selected date range
public function project_staff()
{
  return $this->hasMany(ProjectUser::class)
    ->select([
      ProjectUser::ID,
      ProjectUser::PROJECT_ID,
      ProjectUser::USER_ID,
      User::NAME,
      ProjectUser::ROLE
    ])
    ->whereIn(ProjectUser::STATUS, [
      ProjectUser::STATUS_RESERVED,
      ProjectUser::STATUS_ASSIGNED,
      ProjectUser::STATUS_ACCEPTED
    ])
    ->join(User::TABLE, User::ID, '=', ProjectUser::USER_ID)
    ->with([
      'assignments',
      'shifts'
    ]);
}

// Assignments Model
// Again, just want the assignments and workshifts that fall
// within the selected date range
public function assignments()
{
  return $this->hasMany(Assignment::class, 'projectUser_id')
    ->select([
      Assignment::PROJECT_USER_ID,
      Assignment::START,
      Assignment::FINISH,
      Assignment::TRAVEL_OUT,
      Assignment::TRAVEL_HOME
    ]);
}

public function shifts()
{
  return $this->hasMany(WorkShift::class, ['project_id','user_id'], ['project_id','user_id'])
    ->select([
      WorkShift::ID,
      WorkShift::USER_ID,
      WorkShift::PROJECT_ID,
      WorkShift::ROLE,
      WorkShift::DATE,
      WorkShift::START,
      WorkShift::HOURS,
      WorkShift::STATUS,
      WorkShift::REMARKS
    ]);
}

// WorkShift Model
public function projectUser()
{
    return $this->belongsTo(ProjectUser::class, ['project_id','user_id'], ['project_id','user_id']);
}