查询与指定时段重叠的预订：MySQL及Laravel代码报错排查

问题背景 #

编写查询与指定预订时段重叠记录的SQL及Laravel代码时，第4个AND语句处出现逻辑错误，需求是验证用户指定的预订时段是否可用，并展示所有重叠的预订记录。

数据表结构 #

Schema::create('bookings', function (Blueprint $table) 
{ 
  $table->bigIncrements('id'); 
  $table->datetime('starting_time'); 
  $table->datetime('ending_time')->nullable(); 
  $table->string('guest_name')->nullable(); 
  $table->string('guest_phone')->nullable(); 
  $table->longText('comments')->nullable(); 
  $table->timestamps(); 
  $table->softDeletes(); 
});

Schema::table('bookings', function (Blueprint $table) {
    $table->unsignedBigInteger('room_id')->nullable();
    $table->foreign('room_id', 'room_fk_7600582')->references('id')->on('rooms');
    $table->unsignedBigInteger('team_id')->nullable();
    $table->foreign('team_id', 'team_fk_7547221')->references('id')->on('teams');
});

原错误代码 #

错误SQL语句

select * from `bookings` 
where (
    `room_id` = 4 
       and (`starting_time` < 2022-11-16 23:07:55 
             and `ending_time` > 2022-11-16 23:07:55
             and `starting_time` < 2022-11-17 00:07:55 
             and `ending_time` > 2022-11-17 00:07:55) 
       or (`starting_time` < 2022-11-16 23:07:55 
             and `ending_time` > 2022-11-16 23:07:55
             and `starting_time` < 2022-11-17 00:07:55 
             and `ending_time` < 2022-11-17 00:07:55)
       or ( `starting_time` > 2022-11-16 23:07:55 
             and `ending_time` > 2022-11-16 23:07:55
             and `starting_time` < 2022-11-17 00:07:55 
             and `ending_time` > 2022-11-17 00:07:55)
      ) 

错误Laravel代码

$bookings = Booking::where('room_id', $room_id)
            ->Where(function ($query) use ($times) {
                $query->where('starting_time', '<', $times[0])
                    ->where('ending_time', '>', $times[0])
                    ->where('starting_time', '<', $times[1])
                    ->where('ending_time', '>', $times[1]);
            })
            ->orWhere(function ($query) use ($times) {
                $query->where('starting_time', '<', $times[0])
                    ->where('ending_time', '>', $times[0])
                    ->where('starting_time', '<', $times[1])
                    ->where('ending_time', '<', $times[1]);
            })
            ->orWhere(function ($query) use ($times) {
                $query->where('starting_time', '>', $times[0])
                    ->where('ending_time', '>', $times[0])
                    ->where('starting_time', '<', $times[1])
                    ->where('ending_time', '>', $times[1]);
            })
            ->get();

问题分析 #

1. 语法错误：SQL中的时间值未加单引号，数据库无法识别为datetime类型，这是直接报错的核心原因。
2. 逻辑冗余且覆盖不全：原代码用多分支判断重叠场景，不仅代码冗余，还漏掉了「新预订时段完全包含已有预订」的情况，同时未处理ending_time为null的异常场景。
3. 时段重叠的核心逻辑可简化为：已有预订的开始时间 < 新时段的结束时间，且已有预订的结束时间 > 新时段的开始时间，同时需限定同一房间、排除软删除记录。

修复后的代码 #

修复后SQL语句

SELECT * 
FROM `bookings` 
WHERE `room_id` = 4 
  AND `starting_time` < '2022-11-17 00:07:55' 
  AND `ending_time` > '2022-11-16 23:07:55' 
  AND `ending_time` IS NOT NULL 
  AND `deleted_at` IS NULL;

修复后Laravel代码

$newStartTime = $times[0];
$newEndTime = $times[1];

$bookings = Booking::where('room_id', $room_id)
    ->where('starting_time', '<', $newEndTime)
    ->where('ending_time', '>', $newStartTime)
    ->whereNotNull('ending_time') // 排除结束时间为空的异常记录
    ->get();

补充说明 #

• 验证时段是否可用时，只需判断上述查询结果是否为空：结果为空则时段可用，有记录则存在重叠。
• 若业务允许ending_time为空（比如未结束的预订），可去掉whereNotNull('ending_time')条件，此时只要已有预订的开始时间 < 新时段结束时间，就判定为重叠。

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