MySQL select rows where date not between date

Question

I have a booking system in which I need to select any available room from the database. The basic setup is:

table: room
columns: id, maxGuests

table: roombooking
columns: id, startDate, endDate

table: roombooking_room:
columns: id, room_id, roombooking_id

I need to select rooms that can fit the requested guests in, or select two (or more) rooms to fit the guests in (as defined by maxGuests, obviously using the lowest/closet maxGuests first)

I could loop through my date range and use this sql:

SELECT `id`
FROM `room` 
WHERE `id` NOT IN
(
    SELECT `roombooking_room`.`room_id`
    FROM `roombooking_room`, `roombooking`
    WHERE `roombooking`.`confirmed` =1
    AND DATE(%s) BETWEEN `roombooking`.`startDate` AND `roombooking`.`endDate`
)
AND `room`.`maxGuests`>=%d

Where %$1 is the looped date and %2d is the number of guests to be booked in. But this will just return false if there are more guests than any room can take, and there must be a quicker way of doing this rather than looping with php and running the query?

This is similar to part of the sql I was thinking of: Getting Dates between a range of dates but with Mysql

---

Solution, based on ircmaxwell's answer:

$query = sprintf(
        "SELECT `id`, `maxGuests`
        FROM `room`
        WHERE `id` NOT IN
        (
            SELECT `roombooking_room`.`room_id`
            FROM `roombooking_room`
            JOIN `roombooking` ON `roombooking_room`.`roombooking_id` = `roombooking`.`id`
            WHERE `roombooking`.`confirmed` =1
            AND (`roomBooking`.`startDate` > DATE(%s) OR `roomBooking`.`endDate` < DATE(%s))
        )
        AND `maxGuests` <= %d ORDER BY `maxGuests` DESC",
        $endDate->toString('yyyy-MM-dd'), $startDate->toString('yyyy-MM-dd'), $noGuests);
        $result = $db->query($query);
        $result = $result->fetchAll();

        $rooms = array();
        $guests = 0;
        foreach($result as $res) {
            if($guests >= $noGuests) break;
            $guests += (int)$res['maxGuests'];
            $rooms[] = $res['id'];
        }

Answer 1

Assuming that you are interested to place @Guests from @StartDate to @EndDate

SELECT DISTINCT r.id, 
FROM room r 
     LEFT JOIN roombooking_room rbr ON r.id = rbr.room_id
     LEFT JOIN roombooking ON rbr.roombooking_id = rb.id
WHERE COALESCE(@StartDate NOT BETWEEN rb.startDate AND rb.endDate, TRUE)
      AND COALESCE(@EndDate NOT BETWEEN rb.startDate AND rb.endDate, TRUE)
      AND @Guests < r.maxGuests

should give you a list of all rooms that are free and can accommodate given number of guests for the given period.

NOTES
This query works only for single rooms, if you want to look at multiple rooms you will need to apply the same criteria to a combination of rooms. For this you would need recursive queries or some helper tables. Also, COALESCE is there to take care of NULLs - if a room is not booked at all it would not have any records with dates to compare to, so it would not return completely free rooms. Date between date1 and date2 will return NULL if either date1 or date2 is null and coalesce will turn it to true (alternative is to do a UNION of completely free rooms; which might be faster).

With multiple rooms things get really interesting. Is that scenario big part of your problem? And which database are you using i.e. do you have access to recursive queries?

EDIT

As I stated multiple times before, your way of looking for a solution (greedy algorithm that looks at the largest free rooms first) is not the optimal if you want to get the best fit between required number of guests and rooms.

So, if you replace your foreach with

$bestCapacity = 0;
$bestSolution = array();

for ($i = 1; $i <= pow(2,sizeof($result))-1; $i++) {
    $solutionIdx = $i;
    $solutionGuests = 0;
    $solution = array();
    $j = 0;
    while ($solutionIdx > 0) :
        if ($solutionIdx % 2 == 1) {
            $solution[] = $result[$j]['id'];
            $solutionGuests += $result[$j]['maxGuests'];
        }
        $solutionIdx = intval($solutionIdx/2);
        $j++;
    endwhile;       
    if (($solutionGuests <= $bestCapacity || $bestCapacity == 0) && $solutionGuests >= $noGuests) {
        $bestCapacity = $solutionGuests;
        $bestSolution = $solution;
    }
}

print_r($bestSolution);
print_r($bestCapacity);

Will go through all possible combinations and find the solution that wastes the least number of spaces.

Answer 2

Ok, first off, the inner query you're using is a cartesian join, and will be VERY expensive. You need to specify join criteria (roombooking_room.booking_id = roombooking.id for example).

Secondly, assuming that you have a range of dates, what can we say about that? Well, let's call the start of your range rangeStartDate and rangeEndDate.

Now, what can we say about any other range of dates that does not have any form of overlap with this range? Well, the endDate must not be between be either the rangeStartDate and the rangeEndDate. Same with the startDate. And the rangeStartDate (and rangeEndDate, but we don't need to check it) cannot be between startDate and endDate...

So, assuming %1$s is rangeStartDate and %2$s is rangeEndDate, a comprehensive where clause might be:

WHERE `roomBooking`.`startDate` NOT BETWEEN %1$s AND %2s
    AND `roomBooking`.`endDate` NOT BETWEEN %1$s AND %2$$s
    AND %1s NOT BETWEEN `roomBooking`.`startDate` AND `roomBooking`.`endDate`

But, there's a simpler way of saying that. The only way for a range to be outside of another is for the start_date to be after the end_date, or the end_date be before the start_id

So, assuming %1$s is rangeStartDate and %2$s is rangeEndDate, another comprehensive where clause might be:

WHERE `roomBooking`.`startDate` > %2$s
    OR `roomBooking`.`endDate` < %1$s

So, that brings your overall query to:

SELECT `id`
FROM `room` 
WHERE `id` NOT IN
(
    SELECT `roombooking_room`.`room_id`
    FROM `roombooking_room`
    JOIN `roombooking` ON `roombooking_room`.`roombooking_id` = `roombooking`.`id`
    WHERE `roombooking`.`confirmed` =1
    AND (`roomBooking`.`startDate` > %2$s
        OR `roomBooking`.`endDate` < %1$s)
)
AND `room`.`maxGuests`>=%d

There are other ways of doing this as well, so keep looking...

Answer 3

SELECT rooms.id
FROM rooms LEFT JOIN bookings
ON booking.room_id = rooms.id
WHERE <booking overlaps date range of interest> AND <wherever else>
GROUP BY rooms.id
HAVING booking.id IS NULL

I might be miss remembering how left join works so you might need to use a slightly different condition on the having, maybe a count or a sum.

At worst, with suitable indexes, that should scan half the bookings.