Laravel出勤统计API自定义JSON响应结构实现方案

问题原因 #

当前逻辑把聚合统计字段通过addSelect附加到每条出勤明细查询上，返回结果自然是带重复统计值的明细数组，本质是混淆了「明细查询」和「聚合统计」的查询场景，统计类接口不需要查询单条学生记录，直接用SQL聚合函数计算全局值即可。

实现代码 #

直接替换原getAttendanceReport方法内的逻辑即可，注意如果你的表结构、关联关系和示例有出入，对应调整字段名即可：

public function getAttendanceReport(Request $request)
{
    // 先做入参校验，根据自身业务调整校验规则
    $validated = $request->validate([
        'date' => 'required|date',
        'grade_id' => 'required|integer'
    ]);
    $targetDate = $validated['date'];
    $gradeId = $validated['grade_id'];

    // 统计出勤数据 对应attendance_id=1
    $presentData = AttendanceStudent::where('date', $targetDate)
        ->where('grade_id', $gradeId)
        ->where('attendance_id', 1)
        ->selectRaw('
            COUNT(*) as total_present_students,
            SUM(CASE WHEN s.gender = "男" THEN 1 ELSE 0 END) as total_boys_present,
            SUM(CASE WHEN s.gender = "女" THEN 1 ELSE 0 END) as total_girls_present,
            MAX(attendance_students.created_at) as created_at
        ')
        // 如果AttendanceStudent表本身存了gender字段，去掉join逻辑直接统计本表字段即可
        ->leftJoin('students as s', 'attendance_students.student_id', '=', 's.id')
        ->first();

    // 统计缺勤数据 对应attendance_id=2
    $absentData = AttendanceStudent::where('date', $targetDate)
        ->where('grade_id', $gradeId)
        ->where('attendance_id', 2)
        ->selectRaw('
            COUNT(*) as total_absent_students,
            SUM(CASE WHEN s.gender = "男" THEN 1 ELSE 0 END) as total_boys_absent,
            SUM(CASE WHEN s.gender = "女" THEN 1 ELSE 0 END) as total_girls_absent,
            MAX(attendance_students.created_at) as created_at
        ')
        ->leftJoin('students as s', 'attendance_students.student_id', '=', 's.id')
        ->first();

    // 统计对应年级总人数
    $totalGradeStudents = Student::where('grade_id', $gradeId)->count();

    // 组装要求的响应结构
    return response()->json([
        'message' => '出勤汇总数据获取成功',
        'Present' => [
            'total_present_students' => $presentData->total_present_students ?? 0,
            'total_boys_present' => $presentData->total_boys_present ?? 0,
            'total_girls_present' => $presentData->total_girls_present ?? 0,
            'created_at' => $presentData->created_at ?? null
        ],
        'Absent' => [
            'total_absent_students' => $absentData->total_absent_students ?? 0,
            'total_boys_absent' => $absentData->total_boys_absent ?? 0,
            'total_girls_absent' => $absentData->total_girls_absent ?? 0,
            'created_at' => $absentData->created_at ?? null
        ],
        'total_students_in_grade' => $totalGradeStudents
    ]);
}

返回结构参考 #

接口最终返回的JSON格式完全匹配业务需求：

{
    "message": "出勤汇总数据获取成功",
    "Present": {
        "total_present_students": 126,
        "total_boys_present": 65,
        "total_girls_present": 61,
        "created_at": "2024-05-20 08:30:00"
    },
    "Absent": {
        "total_absent_students": 8,
        "total_boys_absent": 5,
        "total_girls_absent": 3,
        "created_at": "2024-05-20 08:32:00"
    },
    "total_students_in_grade": 134
}

优化说明 #

• 去掉了全量学生明细查询，直接返回聚合结果，数据量越大性能提升越明显
• 增加空值兜底，当天无出勤/缺勤记录时对应统计值默认返回0，不会出现类型错误
• 不存在重复挂载全局统计字段的问题，结构完全匹配前端展示需求

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