Hotel booking system, problem with sql query to find free rooms

Question

this is my DB schema

SELECT r.id FROM room r LEFT JOIN reservation rs ON rs.room_id = r.id
WHERE (rs.date_in < :dateIn AND rs.date_out <= :dateOut)
   OR (rs.date_in >= :dateIn AND rs.date_out > :dateOut)
   OR rs.id IS NULL;

Query to show rooms without reservation

After making reservation for example from 2019-07-12 to 2019-07-14 in room no 1, then making sql query with dateIn 2019-07-12 and dateOut 2019-07-14 , as a response I get room no 1, which is not correct, because in this spread od time reservation has been made.

score 1 · Answer 1 · answered May 12 '19 at 10:36

You want to exclude conflicting reservations.

Let's start by considering the requested period (A...B) and a hypothetical reservation (C...D), we can diagram all the possibilities and see there are several possibilities for conflict:

A.....B  C.....D       OKAY: note B < C never happens otherwise
A........C..B..D       NOT OKAY    (B inside C..D)
A........C.....D..B    NOT OKAY    (C inside A..B, D inside A..B)
         C..A..D..B    NOT OKAY    (A inside C..D, D inside A..B)
         C.A.B.D       NOT OKAY    (A inside C..D, B inside C..D)
         C.....D A..B  OKAY: note A > D never happens otherwise
A...B                  OKAY        (no C...D reservation at all)

So basically no element of the either interval must be inside the other interval:

NOT (
    (:dateIn BETWEEN rs.date_in AND rs.date_out)
    OR
    (:dateOut BETWEEN rs.date_in AND rs.date_out)
    OR
    (rs.date_in BETWEEN :dateIn AND :dateOut)
    OR
    (rs.date_out BETWEEN :dateIn AND :dateOut)
)

and this must include the query failing (no rs entry at all).

Seeing the same diagram, we also see that we can just request that B < C or D < A, trusting implicitly that A < B and C < D, for availability.

rs.date_in > :dateOut OR rs.date_out < :dateIn

And since you want rooms for which there is no conflict, you must put the query above in a JOIN clause designed to find conflicts, with LEFT JOIN and the request for "no conflict" (rs info is NULL) in the WHERE, to catch where the JOIN clause is failing:

SELECT rooms.* 
    FROM rooms 
    LEFT JOIN reservations ON
    (
        rooms.id = reservations.room_id
        AND (
            NOT (reservations.date_in > :dateOut OR reservations.date_out < :dateIn)
        )
    )
) WHERE rooms.id = :id AND reservations.room_id IS NULL

The JOIN clause can be simplified since NOT (A OR B) equals NOT A AND NOT B, and NOT > is <=:

    (
        rooms.id = reservations.room_id
        AND reservations.date_in  <= :dateOut 
        AND reservations.date_out >= :dateIn
    )

which immediately tells us a convenient index for reservations:

    CREATE INDEX reservations_chk ON reservations(room_id, date_in, date_out, rs_id);

You want to test this and verify what happens when a reservation ends on one day and a request starts on that very same day (and the same with reservation starting on a request ending). Depending on hotel policy, you might then want to drop equality from one or both restriction clauses. This is because database days "start" and "end" at midnight, but real-life reservations might begin at, say, 11:00 and end at 10:00 to allow for room cleanup and preparation.

Seeing as such a query is likely to contain keywords SELECT, JOIN, reservation, room, available etc, you can google and find also documents such as this or this, or this on Stack Overflow, which might be of interest (I've only given a cursory look at them - caveat emptor).

score 1 · Answer 2 · answered May 12 '19 at 12:00

I would use NOT EXISTS and the following logic:

select r.id
from room r
where not exists (select 1
                  from reservation rs 
                  where rs.room_id = r.id and
                        rs.date_in < :dateOut and
                        rs.date_out >= :dateIn
                 );

The logic for overlapping time intervals is actually simple -- but perhaps tricky. Two intervals overlap when the following is true:

The first starts before the second ends.
The first ends after the second starts.

Whether the ends are included depend on your particular business rules. The above logic allows someone to check in on the same date that someone else checks out.

You can phrase this as a left join. I find that NOT EXISTS better captures exactly what you are trying to find -- a room with no reservation during a particular period.

score 0 · Answer 3 · answered May 12 '19 at 09:52

0

Your WHERE clause is incorrect, try:

WHERE rs.date_out <= :dateIn OR rs.date_in >= :dateOut OR rs.id IS NULL;

Which means: the current guest will leave before this one arrives or the next guest will arrive after this one leaves.

answered May 12 '19 at 09:52

Titus

22,031
1
23
33

score 0 · Answer 4 · answered May 12 '19 at 09:56

0

You can use NOT EXISTS and apply the conditions:

SELECT r.id FROM room r 
WHERE NOT EXISTS (
  SELECT 1 FROM reservation rs
  WHERE rs.room_id = r.id AND (
    (rs.date_in BETWEEN :dateIn AND :dateOut) OR
    (rs.date_out BETWEEN :dateIn AND :dateOut) OR
    (rs.date_in < :dateIn AND rs.date_out > :dateOut)
  )
)

answered May 12 '19 at 09:56

forpas

160,666
10
38
76

I don't think this is the best logic but it certainly does not merit a downvote. – Gordon Linoff May 12 '19 at 12:02
Maybe it's not the best logic but at least it deserves an explanation. – forpas May 12 '19 at 12:03

Hotel booking system, problem with sql query to find free rooms

4 Answers4