Laravel中如何使用groupBY()和count()统计员工出勤缺勤天数

需要从数据库中获取每位员工的出勤与缺勤天数，已关联tbl_workers和tbl_attendances两张表，期望得到如下格式的结果集：

[
  {
    "worker_id": 1,
    "present_days": "10",
    "absent_days": "10"
  }
]

当前编写的Laravel查询代码无法得到目标格式，代码如下：

$salaryMaster = SalaryMaster::find($id);
$startDate = $salaryMaster->salary_master_to_date;
$endDate = $salaryMaster->salary_master_from_date;
return $data = tbl_worker::join("salary_details", "salary_details.salary_details_worker_id", "tbl_workers.worker_id")
    ->join("tbl_attendances", "tbl_attendances.attendance_worker_id", "tbl_workers.worker_id")
    ->where("salary_details.salary_details_master_table_id", $id)
    ->whereBetween("tbl_attendances.created_at", [Carbon::parse($startDate), Carbon::parse($endDate)])
    ->select(DB::raw('tbl_attendances.attendance_worker_presense,count(*) as count'))
    ->groupBy('tbl_attendances.attendance_worker_presense')
    ->get();

请提供正确的实现方案。

现有代码是按出勤状态分组统计，无法直接输出每个员工的出勤/缺勤天数，调整查询逻辑如下：

$salaryMaster = SalaryMaster::find($id);
$startDate = Carbon::parse($salaryMaster->salary_master_to_date);
$endDate = Carbon::parse($salaryMaster->salary_master_from_date);

$data = tbl_worker::join("salary_details", "salary_details.salary_details_worker_id", "=", "tbl_workers.worker_id")
    ->leftJoin("tbl_attendances", function ($join) use ($startDate, $endDate) {
        $join->on("tbl_attendances.attendance_worker_id", "=", "tbl_workers.worker_id")
             ->whereBetween("tbl_attendances.created_at", [$startDate, $endDate]);
    })
    ->where("salary_details.salary_details_master_table_id", $id)
    ->select(
        "tbl_workers.worker_id",
        DB::raw("COUNT(CASE WHEN tbl_attendances.attendance_worker_presense = 1 THEN 1 END) as present_days"),
        DB::raw("COUNT(CASE WHEN tbl_attendances.attendance_worker_presense = 0 THEN 1 END) as absent_days")
    )
    ->groupBy("tbl_workers.worker_id")
    ->get()
    ->toArray();

return $data;

关键调整说明： #

• 改用leftJoin确保统计周期内无考勤记录的员工也能被查询到，此时出勤/缺勤天数为0
• 使用条件统计：通过CASE WHEN分别统计出勤（假设attendance_worker_presense=1代表出勤）和缺勤（=0代表缺勤）的天数，需根据实际字段值调整判断条件
• 按worker_id分组，保证每个员工对应一条结果
• 用toArray()转换为数组，输出的JSON结构与期望格式一致

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