如何在酒店预订系统中正确查询指定日期范围内可用的房间

本文详解酒店预订系统中房间可用性查询的核心逻辑，通过 left join 与时间重叠判断条件（date_in 搜索开始日），精准筛选无冲突预订的空闲房间，并提供 codeigniter 下安全、可维护的两种实现方案。

在酒店预订系统中，“查询某段时间内可用房间”是一个高频且关键的功能。

其难点不在于联表，而在于正确建模时间冲突逻辑：房间只有在 整个目标入住期间完全未被任何现有订单占用 时才算“可用”。常见误区是用 LIKE 匹配日期（如原代码中 $this->db->like('date_in', $date_in)），这既语义错误（日期是精确值，非模糊文本），又无法表达区间重叠关系。

✅ 正确逻辑是：排除所有与目标时段 [date_in, date_out] 发生时间重叠的预订记录。
两个时间段重叠的充要条件为：

existing_date_in target_date_in

即：已有订单的入住时间早于你的离店时间，且其离店时间晚于你的入住时间 —— 二者同时成立才构成冲突。

基于此，我们构建如下 SQL 查询：

SELECT Rooms.*
FROM Rooms
LEFT JOIN Reservation 
    ON Rooms.roomId = Reservation.rsroomId 
       AND Reservation.date_in < ? 
       AND Reservation.date_out > ?
WHERE Reservation.rsId IS NULL;

该查询含义清晰：对每个房间，尝试关联其在目标时段内存在重叠的所有预订；若关联结果为空（rsId IS NULL），说明该房间在此期间无任何预订，即为可用房。

✅ 推荐实现方式（使用 Query Bindings —— 最安全、最简洁）

CodeIgniter 的查询绑定（Query Bindings）自动处理参数转义，彻底防止 SQL 注入，且语法干净：

function search($date_in, $date_out) {
    $sql = "SELECT Rooms.* 
            FROM Rooms 
            LEFT JOIN Reservation 
                ON Rooms.roomId = Reservation.rsroomId 
                   AND Reservation.date_in < ? 
                   AND Reservation.date_out > ? 
            WHERE Reservation.rsId IS NULL";

    $binds = [$date_out, $date_in]; // 注意顺序：? 依次对应 $date_out 和 $date_in
    return $this->db->query($sql, $binds)->result_array();
}

✅ 优势：无需手动 escape()，参数位置明确，SQL 可读性强，易于单元测试和调试。

⚠️ 备选实现（使用 join() + 手动转义 —— 仅当必须链式调用时）

若坚持使用 Active Record 链式语法，需手动转义并拼接 JOIN 条件（注意空格与括号）：

function search($date_in, $date_out) {
    $date_in_escaped  = $this->db->escape($date_in);
    $date_out_escaped = $this->db->escape($date_out);

    $join_condition = "rooms.roomid = reservation.rsroomId 
                       AND reservation.date_in < {$date_out_escaped} 
                       AND reservation.date_out > {$date_in_escaped}";

    $this->db->select('Rooms.*')
             ->from('Rooms')
             ->join('Reservation', $join_condition, 'left')
             ->where('reservation.rsId IS NULL');

    return $this->db->get()->result_array();
}

⚠️ 注意：->where('reservation.rsId IS NULL') 不能写作 ->where('reservation.rsId', NULL)，后者会被解释为 = NULL（永远为 false），必须显式写 IS NULL。

? 补充说明与最佳实践

• 日期格式统一：确保数据库中 date_in/date_out 为 DATE 或 DATETIME 类型，PHP 传入参数应为 'Y-m-d' 格式字符串（如 '2025-06-15'）；
• 索引优化：为 Reservation(rsroomId, date_in, date_out) 建立复合索引，大幅提升 JOIN 效率；
• 扩展性建议：如需支持按房型名称搜索（如 roomName LIKE '%deluxe%'），应在主查询后追加 ->like('Rooms.roomName', $name)，而非在 JOIN 条件中误用；
• 边界处理：当前逻辑默认采用「入住日当天不可订（因他人可能当日退房）」的保守策略。若业务允许“当日退房后立即入住”，需将条件改为 = 并明确约定时点（如均按 12:00 计）。

掌握这一时间重叠判定模型，不仅适用于酒店房间，还可迁移至会议室、车辆、设备租赁等所有资源预约类系统——核心永远是：用数学逻辑定义“冲突”，再用 SQL 精准表达它。