Retrieving available rooms and their beds for a given date range from database
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'
)