Laravel 8关联数据查询优化：高效构建项目人员排班数据集

我正在构建数据库查询，生成包含项目的数据集，每个项目要关联对应人员、他们的工作分配周期和提交的每日排班时长。

最初的逻辑是：先筛选当月运行的项目，再为每个项目筛选当月在岗人员，接着给每位人员分别发起2次查询获取分配日期和排班详情。虽然能得到需要的数据结构，但大量独立查询导致性能很差，想找用子查询或更高效的实现方式。

更新 #

我已经用模型关联实现了大部分功能，但还有问题：我能在控制器顶层筛选指定日期范围的项目，但需要把这个日期过滤条件也应用到assignments（工作分配）和work_shifts（排班记录）上。也就是只需要获取在指定日期范围内有工作分配的project_users（项目人员），并且只保留符合该日期范围的工作分配和排班记录。

初始代码片段 #

$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();
    }
}

更新后代码片段 #

// 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']);
}

解决方案 #

核心思路是利用**约束预加载（Constrained Eager Loading）**给关联模型添加日期过滤条件，同时通过关联筛选出符合条件的project_users，既避免N+1查询，又能精准过滤数据。

1. 控制器层：传递日期范围并添加关联约束 #

把日期范围提取为变量，通过嵌套闭包在预加载时给所有关联添加过滤条件：

public function load(Request $request)
{
    // 定义日期范围，可根据请求参数动态调整（比如前端传的起止日期）
    $startDate = Carbon::now()->startOfYear();
    $endDate = Carbon::now()->endOfYear();

    $projects = Project::select([
        Project::ID,
        Project::NAME,
        Project::START,
        Project::FINISH
    ])
    ->where(Project::START, '<=', $endDate)
    ->where(Project::FINISH, '>=', $startDate)
    ->with(['project_staff' => function ($query) use ($startDate, $endDate) {
        // 只保留在日期范围内有工作分配的项目人员
        $query->whereHas('assignments', function ($assignQuery) use ($startDate, $endDate) {
            $assignQuery->where('start', '<=', $endDate)
                        ->where('finish', '>=', $startDate);
        })
        // 预加载符合日期范围的工作分配
        ->with(['assignments' => function ($assignQuery) use ($startDate, $endDate) {
            $assignQuery->where('start', '<=', $endDate)
                        ->where('finish', '>=', $startDate);
        },
        // 预加载符合日期范围的排班记录
        'shifts' => function ($shiftQuery) use ($startDate, $endDate) {
            $shiftQuery->where('date', '>=', $startDate)
                       ->where('date', '<=', $endDate);
        },
        // 预加载用户信息
        'user']);
    }])
    ->orderBy(Project::START)
    ->get();

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

2. 调整模型关联，符合ORM规范 #

把手动join改成模型关联，代码更易维护：

Project 模型

public function project_staff()
{
    return $this->hasMany(ProjectUser::class)
        ->select([
            ProjectUser::ID,
            ProjectUser::PROJECT_ID,
            ProjectUser::USER_ID,
            ProjectUser::ROLE
        ])
        ->whereIn(ProjectUser::STATUS, [
            ProjectUser::STATUS_RESERVED,
            ProjectUser::STATUS_ASSIGNED,
            ProjectUser::STATUS_ACCEPTED
        ]);
}

ProjectUser 模型

添加用户关联，替代手动join：

public function user()
{
    return $this->belongsTo(User::class)->select(['id', 'name']);
}

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
        ]);
}

3. 优化点说明 #

• 消除N+1查询：通过嵌套预加载一次性拉取所有关联数据，替代原来的循环查询。
• 精准过滤：用whereHas筛选出符合日期条件的项目人员，不会返回无关数据。
• 动态适配：日期范围提取为变量，可轻松对接前端传参，灵活调整查询范围。
• 规范维护：用模型关联替代手动join，符合Laravel ORM设计思路，代码可读性和可维护性更强。

内容的提问来源于stack exchange，提问作者Lee