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.