如何用单条查询获取指定周的员工排班数据（Laravel场景）

可以合并为单条查询实现，核心思路是提前生成目标周7天对应的日期与星期几映射，将7组查询条件通过OR逻辑拼接在同一个查询语句中，避免循环查询。

实现代码 #

首先预处理目标周的日期信息：

$startDate = new Carbon($data['start_date']);
$startDate->startOfWeek(Carbon::SUNDAY);

// 生成一周7天的日期、对应星期几数组
$weekDays = [];
for ($i = 0; $i <= 6; $i++) {
    $weekDays[] = [
        'date' => $startDate->copy()->format('Y-m-d'),
        'week_day' => $startDate->dayOfWeek
    ];
    $startDate->addDay();
}

执行单条查询获取所有符合条件的排班记录：

$schedules = StaffSchedule::where(function ($query) use ($weekDays) {
    // 把每天的查询条件用OR逻辑拼接
    foreach ($weekDays as $day) {
        $query->orWhere(function ($subQuery) use ($day) {
            $subQuery->where('start_date', '<=', $day['date'])
                ->where('day_of_week', $day['week_day'])
                ->where(function ($endCondition) use ($day) {
                    $endCondition->where('end_date', '>=', $day['date'])
                        ->orWhereNull('end_date');
                });
        });
    }
})
->orderBy('user_id')
->get();

如果你需要和原有逻辑一样按天分组存储结果，可以对查询返回的集合做二次匹配分组即可。

生成的SQL结构参考 #

SELECT * FROM `schedules` 
WHERE (
    (start_date <= '2021-06-13' AND day_of_week = 0 AND (end_date >= '2021-06-13' OR end_date IS NULL))
    OR (start_date <= '2021-06-14' AND day_of_week = 1 AND (end_date >= '2021-06-14' OR end_date IS NULL))
    OR -- 剩余5天的条件依次拼接
)
ORDER BY `user_id` ASC

优化建议 #

1. 建议将表中start_date、end_date字段的类型从VARCHAR改为DATE，既可以提升查询性能，也能避免字符串格式异常导致的日期比较错误。
2. 可以新增(day_of_week, start_date, end_date)联合索引，大幅提升该查询的执行速度。

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