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'
|||||||||||||||||||||||||||||||||
|| rooms_id || bed_number ||
||||||||||||||||||||||||||||
|| 1 || 1 ||
----------------------------
|| 1 || 2 ||
----------------------------
|| 2 || 5 ||
----------------------------
|| 2 || 6 ||
----------------------------
|| 2 || 7 ||
----------------------------
|| 2 || 8 ||
----------------------------
SELECT *
FROM `rooms`
WHERE `id` NOT IN
(SELECT rooms_id FROM reservations WHERE `to` > '2016-02-18' AND `from` < '2016-02-24')
| room_id | title | beds_total | available_bed_nrs |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| 1 | luxury room | 2 | 1, 2 |
| 2 | dorm room | 8 | 5, 6, 7, 8 |
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'
)