如何在PHP Laravel中检测用户工作时间段是否重叠

我需要实现一个功能：检查特定用户的给定时间段是否与数据库中已有的工作时段存在重叠，若存在重叠则函数返回true。但我写的checkExistingWorkingHours函数无法正常工作——当传入测试参数checkExistingWorkingHours(1, '2023-06-01', '00:15', '2023-06-01', '03:00')时，数据库中仅存在2023-06-01 03:30至23:00的工作时段，明明无重叠，函数却判定存在重叠。

原问题代码 #

function checkExistingWorkingHours($user_id, $start_date, $start_time, $end_date, $end_time)
{
    // dd($user_id . " " . $start_date . " " . $start_time . " " . $end_date . " " . $end_time);

    $startDateTime = Carbon::parse($start_date . ' ' . $start_time);
    $endDateTime = Carbon::parse($end_date . ' ' . $end_time);

    $overlapExists = WorkingHour::where('user_id', $user_id)
    ->where(function ($query) use ($startDateTime, $endDateTime) {
        $query->where(function ($query) use ($startDateTime, $endDateTime) {
            // Check for overlap where the start_datetime is between existing start_datetime and end_datetime
            $query->where(function ($query) use ($startDateTime, $endDateTime) {
                $query->where('start_date', '=', $startDateTime->format('Y-m-d'))
                    ->where('start_time', '<', $endDateTime->format('H:i'))
                    ->where('end_date', '=', $startDateTime->format('Y-m-d'))
                    ->where('end_time', '>', $startDateTime->format('H:i'));
            })->orWhere(function ($query) use ($startDateTime, $endDateTime) {
                $query->where('start_date', '<', $startDateTime->format('Y-m-d'))
                    ->where('end_date', '=', $startDateTime->format('Y-m-d'))
                    ->where('end_time', '>', $startDateTime->format('H:i'));
            });
        })->orWhere(function ($query) use ($startDateTime, $endDateTime) {
            // Check for overlap where the end_datetime is between existing start_datetime and end_datetime
            $query->where(function ($query) use ($startDateTime, $endDateTime) {
                $query->where('start_date', '=', $endDateTime->format('Y-m-d'))
                    ->where('end_date', '>=', $endDateTime->format('Y-m-d'))
                    ->where('start_time', '<', $endDateTime->format('H:i'));
            })->orWhere(function ($query) use ($startDateTime, $endDateTime) {
                $query->where('start_date', '<=', $startDateTime->format('Y-m-d'))
                    ->where('end_date', '>=', $endDateTime->format('Y-m-d'));
            });
        });
    })->exists();

    return $overlapExists;
}

问题分析 #

函数逻辑存在核心漏洞：

1. 最后一个orWhere条件where('start_date', '<=', $startDateTime->format('Y-m-d'))->where('end_date', '>=', $endDateTime->format('Y-m-d'))完全忽略时间部分，只要数据库中记录的日期范围包含当前查询的日期范围（哪怕时间完全不重叠），就会判定为存在重叠——这正是测试用例误判的原因，数据库里的2023-06-01记录满足该日期条件，直接返回true。
2. 拆分日期和时间的判断逻辑过于复杂，容易遗漏跨天时段、边界时间等特殊场景的重叠判定。

正确的思路是：将数据库中的日期和时间字段合并为完整的DateTime，然后使用时段重叠的通用判定规则：两个时段[A_start, A_end]和[B_start, B_end]存在重叠的必要条件是 A_start < B_end 且 B_start < A_end。

修复后的代码 #

直接数据库合并字段查询 #

function checkExistingWorkingHours($user_id, $start_date, $start_time, $end_date, $end_time)
{
    $newStart = Carbon::parse("$start_date $start_time");
    $newEnd = Carbon::parse("$end_date $end_time");

    $overlapExists = WorkingHour::where('user_id', $user_id)
        ->whereRaw("STR_TO_DATE(CONCAT(start_date, ' ', start_time), '%Y-%m-%d %H:%i') < ?", [$newEnd])
        ->whereRaw("STR_TO_DATE(CONCAT(end_date, ' ', end_time), '%Y-%m-%d %H:%i') > ?", [$newStart])
        ->exists();

    return $overlapExists;
}

更优雅的模型访问器实现 #

先在WorkingHour模型中添加访问器，自动合并日期和时间为Carbon实例：

class WorkingHour extends Model
{
    // ...其他模型代码

    public function getStartDateTimeAttribute()
    {
        return Carbon::parse($this->start_date . ' ' . $this->start_time);
    }

    public function getEndDateTimeAttribute()
    {
        return Carbon::parse($this->end_date . ' ' . $this->end_time);
    }
}

再简化查询逻辑：

function checkExistingWorkingHours($user_id, $start_date, $start_time, $end_date, $end_time)
{
    $newStart = Carbon::parse("$start_date $start_time");
    $newEnd = Carbon::parse("$end_date $end_time");

    $overlapExists = WorkingHour::where('user_id', $user_id)
        ->whereRaw("start_datetime < ?", [$newEnd])
        ->whereRaw("end_datetime > ?", [$newStart])
        ->exists();

    return $overlapExists;
}

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