如何优化按月/日期范围循环的考勤统计查询以减少查询次数？

问题背景 #

在Laravel项目中，控制器3次调用attendanceData函数，每次针对月份的30/31天执行一次查询，单页总共产生90-93次查询。原函数通过循环日期数组，每日执行一次关联Employee模型的count统计，最终输出按日期顺序的数值数组。

调用参数如下：

$absentresult = attendanceData($dates, [4], $department = false);
$totalPresentResult = attendanceData($dates, [1, 2, 3, 5], $department = 3);
$totalAbsentResult = attendanceData($dates, [4], $department = 3);

原函数代码：

function attendanceData($dates, $status_id, $department = false)
{
    $attendanceresult = [];
    foreach($dates as $date)
    {
        if($department)
        {
            $presents = Attendance::with('Employee', function($q) use($department)
                                    {
                                        $q->where('department_id', '=', $department)
                                            ->where('designation_id', '!=', 26);
                                    })
                                    ->whereDate('in_time', $date)
                                    ->whereIn('attendance_status_id', $status_id)
                                    ->count();
        }
        else
        {
            $presents = Attendance::with('Employee', function($q)
                                    {
                                        $q->where('department_id', '!=', 4);
                                    })
                                    ->whereDate('in_time', $date)
                                    ->whereIn('attendance_status_id', $status_id)
                                    ->count();
        }

        if($presents)
        {
            $attendanceresult[] = $presents;
        }else
        {
            $attendanceresult[] = 0;
        }
    }

    return $attendanceresult;
}

要求输出格式示例：

array:10 [
 0 => 7
 1 => 0
 2 => 7
 3 => 8
 4 => 3
 5 => 12
 6 => 13
 7 => 12
 8 => 0
 9 => 12
]

优化思路 #

核心是把多次单日期查询合并为一次批量查询，通过分组统计得到所有日期的结果，再映射到目标日期数组中补全0值，将原本90+次查询压缩为3次（对应3次函数调用），甚至可以进一步合并3次调用为1次查询。

具体实现步骤 #

• 移除循环中的单日期查询，改为一次性筛选所有目标日期的数据
• 使用join关联Employee表替代with，避免N+1查询问题，提升统计效率
• 通过groupBy按日期分组，配合selectRaw直接统计每组记录数
• 将查询结果转为日期为键的关联数组，遍历目标日期数组补全无数据日期的0值

优化后的函数代码 #

function attendanceData($dates, $status_ids, $department = false)
{
    // 初始化查询构造器，关联Employee表
    $query = Attendance::join('employees', 'attendances.employee_id', '=', 'employees.id');

    // 处理部门筛选条件
    if ($department) {
        $query->where('employees.department_id', $department)
              ->where('employees.designation_id', '!=', 26);
    } else {
        $query->where('employees.department_id', '!=', 4);
    }

    // 筛选目标日期和考勤状态，分组统计
    $results = $query->whereIn('attendance_status_id', $status_ids)
                     ->whereIn(\DB::raw('date(in_time)'), $dates)
                     ->selectRaw('date(in_time) as attendance_date, count(*) as total')
                     ->groupBy('attendance_date')
                     ->pluck('total', 'attendance_date')
                     ->toArray();

    // 遍历目标日期数组，补全0值，保证顺序和输入一致
    $attendanceResult = [];
    foreach ($dates as $date) {
        $attendanceResult[] = $results[$date] ?? 0;
    }

    return $attendanceResult;
}

进一步优化：合并3次调用为1次查询 #

如果想把3次函数调用合并成1次查询，可创建如下函数一次性获取所有统计数据：

function getAllAttendanceData($dates)
{
    $baseQuery = Attendance::join('employees', 'attendances.employee_id', '=', 'employees.id');

    // 1. 全局缺勤统计（department=false, status=4）
    $globalAbsent = $baseQuery->clone()
                          ->where('employees.department_id', '!=', 4)
                          ->where('attendance_status_id', 4)
                          ->whereIn(\DB::raw('date(in_time)'), $dates)
                          ->selectRaw('date(in_time) as date, count(*) as total')
                          ->groupBy('date')
                          ->pluck('total', 'date')
                          ->toArray();

    // 2. 部门3的出勤统计（status=1,2,3,5）
    $dept3Present = $baseQuery->clone()
                          ->where('employees.department_id', 3)
                          ->where('employees.designation_id', '!=', 26)
                          ->whereIn('attendance_status_id', [1,2,3,5])
                          ->whereIn(\DB::raw('date(in_time)'), $dates)
                          ->selectRaw('date(in_time) as date, count(*) as total')
                          ->groupBy('date')
                          ->pluck('total', 'date')
                          ->toArray();

    // 3. 部门3的缺勤统计（status=4）
    $dept3Absent = $baseQuery->clone()
                         ->where('employees.department_id', 3)
                         ->where('employees.designation_id', '!=', 26)
                         ->where('attendance_status_id', 4)
                         ->whereIn(\DB::raw('date(in_time)'), $dates)
                         ->selectRaw('date(in_time) as date, count(*) as total')
                         ->groupBy('date')
                         ->pluck('total', 'date')
                         ->toArray();

    // 映射到日期数组，补全0值
    $absentResult = [];
    $totalPresentResult = [];
    $totalAbsentResult = [];

    foreach ($dates as $date) {
        $absentResult[] = $globalAbsent[$date] ?? 0;
        $totalPresentResult[] = $dept3Present[$date] ?? 0;
        $totalAbsentResult[] = $dept3Absent[$date] ?? 0;
    }

    return compact('absentResult', 'totalPresentResult', 'totalAbsentResult');
}

使用时只需调用一次：

$allResults = getAllAttendanceData($dates);
$absentresult = $allResults['absentResult'];
$totalPresentResult = $allResults['totalPresentResult'];
$totalAbsentResult = $allResults['totalAbsentResult'];

此方案将原本90+次查询压缩为1次，性能提升显著。

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