CODEWITHSUNDEEP
mysqlsqldatabase
user1287145
user1287145

Reputation:

SQL query to select all rooms from a table which still have free capacity in date range

I'm developing a booking system for hostels. Each room has a different amount of beds inside (room.capacity). It's possible that different reservations share a single room with three beds for example. It's also possible that a single reservation takes up a whole room and it's capacity, or even take up multiple beds from different rooms. The idea is, rather than in a classical hotel booking system where you book on a 'per-room' basis, in my system all works on a 'per-bed' basis. Basically in the backend you add a new room and define a capacity, rather than adding every single bed. It it's not necessary to know who has which bed, but it's important to know which room and if the capacity limit is reached or if there are still rooms left in a room.

I'm looking for a way to select ALL ROOMS with their CAPACITY + AVAILABLE BEDS depending on the entries in reservation_room.

I tried around for hours with a massive query like:

SELECT room.id, room.name, room.room_category_id, room.status, room.capacity 
FROM room
WHERE (SELECT COUNT(reservation_room.id) 
       FROM reservation_room 
       WHERE reservation_room.id = room.id) AND 
WHERE (SELECT reservation.start, reservation.end 
       FROM reservation 
       WHERE NOT reservation.start > date$1 AND
             NOT reservation.end < $date2 
      UNION 
      SELECT COUNT(id) AS beds_available 
      FROM reservation_room 
      WHERE reservation_room.id = room.id;

table layout

Upvotes: 3

Views: 1984

Answers (1)

Barmar
Barmar

Reputation: 780688

I think this is pretty close:

SELECT r.id, r.name, r.room_category_id, r.status, r.capacity, r.capacity - IFNULL(COUNT(res.id), 0) AS beds_available
FROM room AS r
LEFT JOIN reservation_room as rr ON rr.room_id = r.id
LEFT JOIN reservation as res
     ON res.id = rr.reservation_id
     AND @range_start < res.end
     AND @range_end > res.start
GROUP BY r.id

The only problem is if there are two non-overlapping reservations for the same room during the period, it will decrease the available beds twice. I'm not sure how to sort all the reservations so that only overlapping reservations add to the bed use count.

Upvotes: 1

Related Questions