Laravel Eager Load关联查询如何基于父用户ID过滤worktime子关联

有两种常用实现方式，可根据你的业务数据量选择：

方案1：遍历挂载（适合中小数据量，逻辑清晰易维护） #

先查询用户和符合时间范围的任务，再遍历为每个任务单独加载对应用户的工时数据：

$users = User::select('users.id', 'users.first_name', 'users.last_name', 'users.contract_type')
    ->with([
        'tasks' => function($query) use ($from, $to){
            $query->whereBetween('date', [$from, $to])
                ->select('tasks.id', 'tasks.date');
        }
     ])
     ->get();

// 为每个用户的任务加载仅属于该用户的工时数据
$users->each(function ($user) {
    $user->tasks->each(function ($task) use ($user) {
        $task->load(['worktimes' => function($query) use ($user) {
            $query->where('user_id', $user->id)
                ->withCount([
                    'tags as late_tag_count' => function ($subQuery) {
                        $subQuery->where('tags.id', 1);
                    },
                    'tags as early_tag_count' => function ($subQuery) {
                        $subQuery->where('tags.id', 2);
                    },
                    'tags as others_tag_count' => function ($subQuery) {
                        $subQuery->where('tags.id', 3);
                    }
                ]);
        }]);
    });
});

方案2：批量预加载手动挂载（适合大数据量，性能更优） #

仅执行4次查询即可完成所有数据加载，避免循环查询产生N+1问题：

// 第一步：查询用户和符合条件的任务
$users = User::select('users.id', 'users.first_name', 'users.last_name', 'users.contract_type')
    ->with([
        'tasks' => function($query) use ($from, $to){
            $query->whereBetween('date', [$from, $to])
                ->select('tasks.id', 'tasks.date');
        }
     ])
     ->get();

// 第二步：收集所有需要查询的用户+任务组合
$userTaskConditions = $users->flatMap(function ($user) {
    return $user->tasks->map(fn($task) => [
        'user_id' => $user->id,
        'task_id' => $task->id
    ]);
});

if ($userTaskConditions->isNotEmpty()) {
    // 第三步：批量查询所有符合条件的工时数据，附带标签计数
    $worktimes = Worktime::query()
        ->where(function ($query) use ($userTaskConditions) {
            foreach ($userTaskConditions as $condition) {
                $query->orWhere(fn($q) => $q->where($condition));
            }
        })
        ->withCount([
            'tags as late_tag_count' => fn($subQuery) => $subQuery->where('tags.id', 1),
            'tags as early_tag_count' => fn($subQuery) => $subQuery->where('tags.id', 2),
            'tags as others_tag_count' => fn($subQuery) => $subQuery->where('tags.id', 3)
        ])
        ->get()
        ->groupBy(['task_id', 'user_id']); // 按任务ID和用户ID分组，方便后续匹配

    // 第四步：将工时数据手动挂载到对应任务下
    $users->each(function ($user) use ($worktimes) {
        $user->tasks->each(function ($task) use ($user, $worktimes) {
            $task->setRelation('worktimes', $worktimes->get($task->id)?->get($user->id) ?? collect());
        });
    });
}

关联优化建议 #

你当前Worktime模型里的users多对多关联定义不符合业务逻辑，单条工时记录仅属于一个用户，建议修正为：

// Worktime.php
public function user()
{
    return $this->belongsTo(User::class);
}

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