从数据库中获取可用房间和给定日期的床位 [英] Retrieving available rooms and their beds for a given date range from database
问题描述
我已经阅读了关于这个话题的所有帖子，但是我的问题有一个额外的收获，我需要每个房间的床位，因为它的宿舍和一些房间是共享的。
现在我试过在
预订
现在我用来检索所有预留房间和床的查询是
SELECT rooms_id，bed
FROM reservation
WHERE`to`> '2016-02-18'AND`from`< '$ 2 $'
如果我的输入变量$和第二个日期是第一个日期输入变量$，并且它允许不仅检索具有从$开始的预留的房间和从$开始的所有预订，而且在日期范围之前开始并且在内部结束之前开始的所有预订在内部开始并在日期范围之后结束，并且最终保留在日期范围之前开始和结束。所以上面这个确切的查询将返回以下表格
我可以在我的应用程序中可视化这样
但这是我被卡住的地方。我不知道如何匹配我的数据，以找到所有可用的房间，但还有床。
所需表格可用房间的日期范围为2至2，应为：
||||||||||||||||||||||||||||||||||||||||||||||||
|| 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 reservation WHERE`to`>'2016-02-18'AND`from`<'2'24'）
而不是我上面描述的/> b
$ b
感谢任何关于如何处理这方面的提示和想法。
我的一部分担心，这一切都会归结于我，不把床作为实体，不得不这样做，尽管我将不会将存储在床上的任何信息例如他们的颜色，位置，质量，价格等...
回复@ Paul-Spiegel
令人惊奇的是，有没有什么办法可以获得免费床位，而不是总数。因为那个人预订时，我必须把它分配到床上。所以如果结果可以是
| room_id |标题| bed_total | available_bed_nrs |
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||| ||||||||||||
| 1 |豪华房| 2 | 1，2 |
| 2 |宿舍| 8 | 5，6，7，8 |
而不是
您可以使用此方式获得一些免费房间查询：
set @from：=' -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 reservation 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> @从;
按房间分组，计算预留房间的数量，并将所有数量的预留房间存储在一个字符串字段中：
SELECT rv.rooms_id，
COUNT（1）as num_reserved_beds，
GROUP_CONCAT（rv.bed）as reserved_bed_nrs
FROM reservation rv
WHERE rv.from< @to
AND rv.to> @from
GROUP BY rv.rooms_id
加入（LEFT JOIN）客房与给定的结果计算免费床数，并将其与您想要预订的床数进行比较。
更新如何获得免费（不保留）床：
如果您没有所有现有床铺的桌子，您将需要某种类型的sequece数字。假设一个房间最多可以有100张床，你可以创建一个序列表，其中有100个数字：
CREATE TABLE`sequence`（
`nr` TINYINT（3）UNSIGNED NOT NULL，
PRIMARY KEY（`nr`）
）从
中选择d1.d * 10 + d0.d + 1作为nr（选择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选择7联合所有选择8联合全部选择9）d0，
（选择0 d联合全部选择1联合全部选择2联合全部选择3联合全部选择4联合全部选择5联合全部选择6联合全部选择7联盟所有选择8联合所有选择9）d1
现在可以列出所有现有的床通过交叉加入 c> c> c
SELECT *
从房间rm
CROSS JOIN序列seq
WHERE s eq.nr< = rm.beds
列出所有不resverd 床可以结合预订床的查询（选择所有预订日期范围内不保留的床）：
SELECT *
从房间rm
CROSS JOIN序列seq
WHERE seq.nr< = rm.beds
AND（rm.id，seq。 nr）NOT IN（
SELECT rv.rooms_id，rv.bed
FROM reservation rv
WHERE rv.from< '2016-02-24'
AND rv.to> '$ 2 $'
这也可以用 NOT EXISTS 或不包括 LEFT JOIN 。
您还可以跳过创建序列表使用创建代码作为子选择：
SELECT *
从房间rm
CROSS JOIN（
从
中选择d1.d * 10 + d0.d + 1为nr
（选择0 d联合全部选择1联合全部选择2联合全部选择3联合全部选择4联合全部选择5联合全部选择6联合全部选择7联合全部选择8联合全部选择9）d0，
（选择0 d所有选择1联合所有选择2联合全部选择3联合全部选择4联合全部选择5联合全部选择6联合所有选择7联合全部选择8联合全部选择9）d1
）seq
WHERE seq .nr< = rm.beds
AND（rm.id，seq.nr）NOT IN（
SELECT rv.rooms_id，rv.bed
FROM reservation rv
WHERE rv.from< '2016-02-24'
AND rv.to> '$ 2 $'
http://sqlfiddle.com/#!9/a0d61/5
I have read carefuly through all the posts on here about this topic, but my question has one extra catch, where I need to get available beds of each room because its a hostel and some rooms are shared.
Now I have tried doing this in PHP, but then realized I haven't even taken in account the date range. So now I am thinking since I already have a query that retrieves all reservations occurring within a given date range and then compare the retrieved rooms and their beds with the room table and show only rooms and their beds that are not occupied. But I can't figure out how to work around the beds because they are not an entity, only a count of total beds in each room. But then a reservation says for which bed in the room that reservation is made..
Here are my tables
Rooms
Reservations
Now the query that I use to retrieve all reserved rooms and their beds is
SELECT rooms_id, bed
FROM reservations
WHERE `to` > '2016-02-18' AND `from` < '2016-02-24'
The first date if my input variable $from and the second date is input variable $to and it allows to retrieve not only rooms that have a reservation that starts within $from and $to but also all reservations that started before the date range and end inside, started inside and end after the date range and finally reservations that started before and end after the date range. So this exact query above would return the following table
which I can then visualize in my app like this
But this is where I get stuck. I have no idea how to match the data I have in order to find all available rooms BUT ALSO THE BEDS. The desired table AVAILABLE ROOMS for the date range from '2016-02-18' and to '2016-02-24' should look like this:
|||||||||||||||||||||||||||||||||
|| rooms_id || bed_number ||
||||||||||||||||||||||||||||
|| 1 || 1 ||
----------------------------
|| 1 || 2 ||
----------------------------
|| 2 || 5 ||
----------------------------
|| 2 || 6 ||
----------------------------
|| 2 || 7 ||
----------------------------
|| 2 || 8 ||
----------------------------
You can see this in the picture where I show how it looks in my app. The only rooms and beds that are available between the two dates are the Luxury Room and its bed number 1 and bed number 2 and the Dorm rooms beds 5,6,7,8 because 1-4 have a reservation occurring on at least on one of the desired dates
The only idea I had was using NOT IN, but that only works if I didn't care about the beds and also the output is of here it is
SELECT *
FROM `rooms`
WHERE `id` NOT IN
(SELECT rooms_id FROM reservations WHERE `to` > '2016-02-18' AND `from` < '2016-02-24')
instead of what I "sketched" above
I'd appreciate any tips and ideas on how approach this. Part of me worries that this will all come down to me not treating the beds as entities and having to do that, even though as I will never be storing any sort of information on the beds such as their color, position, quality, price, etc...
REPLY TO @Paul-Spiegel
That is amazing, but is there any way to get the free beds as numbers as well instead of total number. Because then when the person makes a reservation I have to assign it to on of the beds. So if the result could be
| room_id | title | beds_total | available_bed_nrs |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| 1 | luxury room | 2 | 1, 2 |
| 2 | dorm room | 8 | 5, 6, 7, 8 |
instead of
You can get a number of free rooms (and the reserved room numbers) with this query:
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
You can now parse reserved_bed_nrs, loop over all beds per room and pick the beds that are not in reserved_bed_nrs.
Explaination:
Get all beds reserved within date range (excluding):
SELECT *
FROM reservations r
WHERE r.from < @to
AND r.to > @from;
Group by room, count the number of reserved rooms and store all numbers of reserved rooms in one string field:
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
Join (LEFT JOIN) rooms with the given result calculate the number of free beds and compare it with the number of beds you want to book.
Update How to get free (not reserved) beds:
If you don't have a table with all existing beds, you will need some kind of sequece numbers. Assuming a room can have a maximum of 100 beds you can create a sequence table with 100 numbers:
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
Now it's possible to list all existing beds by cross joining the tables rooms and sequence:
SELECT *
FROM rooms rm
CROSS JOIN sequence seq
WHERE seq.nr <= rm.beds
To list all not resverd beds you can combine it with a query for reserved beds (select all beds that are not reserved within the booking date range):
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'
)
This can also be done with NOT EXISTS or excluding LEFT JOIN.
You also can skip the creation of the sequence table use the creation code as subselect:
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
这篇关于从数据库中获取可用房间和给定日期的床位的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
