php - 从数据库中检索给定日期范围内的可用房间及其床位

Question

我已经仔细阅读了这里关于这个主题的所有帖子，但我的问题有一个额外的问题，我需要获得每个房间的可用床位，因为它是一个旅馆，有些房间是共用的。

现在我已经尝试在 PHP 中这样做了，但后来意识到我什至没有考虑日期范围。所以现在我在想，因为我已经有一个查询来检索在给定日期范围内发生的所有预订，然后将检索到的房间及其床位与房间表进行比较，并仅显示未占用的房间及其床位。但我无法弄清楚如何在床周围工作，因为它们不是一个实体，只是每个房间的床总数。但是随后预订说预订的是房间里的哪张床..

这是我的表格

房间

预订

现在我用来检索所有预订房间及其床位的查询是

SELECT rooms_id, bed 
FROM reservations 
WHERE `to` > '2016-02-18' AND `from` < '2016-02-24'

第一个日期，如果我的输入变量是 $from，第二个日期是输入变量 $to，它不仅允许检索在 $from 和 $to 内开始预订的房间，还可以检索在该日期之前开始的所有预订范围内结束，在日期范围内开始并在日期范围之后结束，最后是在日期范围之前开始并在日期范围之后结束的预订。所以上面这个确切的查询将返回下表

然后我可以像这样在我的应用程序中可视化

但这就是我卡住的地方。我不知道如何匹配我拥有的数据以找到所有可用的房间，但也包括床。从“2016-02-18”到“2016-02-24”的日期范围内所需的表AVAILABLE ROOMS应该如下所示:

|||||||||||||||||||||||||||||||||
|| rooms_id || bed_number ||
||||||||||||||||||||||||||||
||    1     ||    1       ||
----------------------------
||    1     ||    2       ||
----------------------------
||    2     ||    5       ||
----------------------------
||    2     ||    6       ||
----------------------------
||    2     ||    7       ||
----------------------------
||    2     ||    8       ||
----------------------------

您可以在我展示它在我的应用程序中的外观的图片中看到这一点。在这两个日期之间唯一可用的房间和床位是豪华房及其床号 1 和床号 2 以及宿舍床位 5、6、7、8，因为 1-4 至少有一个预订所需的日期

我唯一的想法是使用 NOT IN，但这只有在我不关心床的情况下才有效，而且输出就在这里

SELECT *
FROM `rooms`
WHERE `id` NOT IN
     (SELECT rooms_id FROM reservations WHERE `to` > '2016-02-18' AND `from` < '2016-02-24')

而不是我在上面“勾画”的内容

对于如何处理此问题的任何提示和想法，我将不胜感激。我的一部分担心这将归结为我没有将床视为实体并不得不这样做，即使我永远不会存储床上的任何类型的信息，例如它们的颜色、定位、质量、价格等...

回复@Paul-Spiegel

太棒了，但是有什么办法可以得到免费床位的数量而不是总数。因为那样的话，当这个人进行预订时，我必须将其分配给其中一张床。所以如果结果可能是

| room_id | title       | beds_total | available_bed_nrs |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| 1       | luxury room | 2          | 1, 2              |
| 2       | dorm room   | 8          | 5, 6, 7, 8        |

代替

Answer 1

您可以通过以下查询获得一些空闲房间(以及预订的房间号):

set @from := '2016-02-18';
set @to   := '2016-02-24';
set @beds := 1;

SELECT rm.id, rm.title, rm.beds,
    rm.beds - IFNULL(rv.num_reserved_beds, 0) AS num_free_beds,
    rv.reserved_bed_nrs
FROM rooms rm
LEFT JOIN (
    SELECT rv.rooms_id, 
        COUNT(1) as num_reserved_beds, 
        GROUP_CONCAT(rv.bed) as reserved_bed_nrs
    FROM reservations rv
    WHERE rv.from < @to
      AND rv.to   > @from
    GROUP BY rv.rooms_id
) rv ON rv.rooms_id = rm.id
HAVING num_free_beds >= @beds

您现在可以解析 reserved_bed_nrs，遍历每个房间的所有床位并选择不在 reserved_bed_nrs 中的床位。

解释:

获取日期范围内(不包括)的所有床位预订:

SELECT *
FROM reservations r
WHERE r.from < @to
  AND r.to   > @from;

按房间分组，统计预订房间数，并将所有预订房间数存储在一个字符串字段中:

SELECT rv.rooms_id, 
    COUNT(1) as num_reserved_beds, 
    GROUP_CONCAT(rv.bed) as reserved_bed_nrs
FROM reservations rv
WHERE rv.from < @to
  AND rv.to   > @from
GROUP BY rv.rooms_id

用给定的结果加入(LEFT JOIN)rooms，计算空闲床位的数量，并将其与您要预订的床位数量进行比较。

更新如何获得免费(未预订)床位:

如果您没有包含所有现有床位的表格，您将需要某种序列号。假设一个房间最多可以有 100 张床，您可以创建一个包含 100 个数字的 sequence 表:

CREATE TABLE `sequence` (
    `nr` TINYINT(3) UNSIGNED NOT NULL,
    PRIMARY KEY (`nr`)
) select d1.d*10 + d0.d + 1 as nr   from 
    (select 0 d union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) d0,
    (select 0 d union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) d1

现在可以通过交叉连接表 rooms 和 sequence 来列出所有现有床位:

SELECT *
FROM rooms rm 
CROSS JOIN sequence seq
WHERE seq.nr <= rm.beds

要列出所有未预订床位，您可以将其与预订床位查询结合使用(选择在预订日期范围内未预订的所有床位):

SELECT *
FROM rooms rm 
CROSS JOIN sequence seq
WHERE seq.nr <= rm.beds
  AND (rm.id, seq.nr) NOT IN (
        SELECT rv.rooms_id, rv.bed
        FROM reservations rv
        WHERE rv.from < '2016-02-24'
          AND rv.to   > '2016-02-18'
  )

这也可以通过 NOT EXISTS 或排除 LEFT JOIN 来完成。

您也可以跳过 sequence 表的创建，使用创建代码作为子选择:

SELECT *
FROM rooms rm 
CROSS JOIN (
    select d1.d*10 + d0.d + 1 as nr
    from 
    (select 0 d union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) d0,
    (select 0 d union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) d1
) seq
WHERE seq.nr <= rm.beds
  AND (rm.id, seq.nr) NOT IN (
        SELECT rv.rooms_id, rv.bed
        FROM reservations rv
        WHERE rv.from < '2016-02-24'
          AND rv.to   > '2016-02-18'
  )

http://sqlfiddle.com/#!9/a0d61/5