MySQL日期范围查询优化：结合Checksheets表完善房间可用性判断

Hey there, it looks like you ran into a logic gap when adding the checksheets condition to your room availability query. The original OR clause was causing rooms to show up as available even when there were active conflicting bookings. Let's break down the problem and fix it properly.

Clarifying the Requirements #

We need rooms that are available during the target time window, which means:

• Either the room has no conflicting approved/completed bookings that aren't fully resolved
• Or any conflicting bookings are marked as Completed and the keys were returned before the target window starts, with no other active conflicting bookings.

Your initial approach with OR was flawed because it only checked for one valid completed booking, ignoring any other conflicting approved bookings that would make the room unavailable.

The Corrected Query Approach #

Instead of trying to include valid completed bookings with an OR, we should first identify all rooms that are unavailable due to:

1. Conflicting bookings marked as Approved
2. Conflicting bookings marked as Completed where keys weren't returned before the target window starts

Then we simply exclude those unavailable rooms from our results.

Here's the revised SQL:

-- Set your target time range parameters
SET @target_start = '2018-01-15 12:00:00';
SET @target_end = '2018-01-16 12:00:00';

SELECT rooms.*
FROM rooms
WHERE rooms.id NOT IN (
    SELECT DISTINCT b.room_id
    FROM bookings b
    JOIN statuses s ON s.id = b.status_id
    LEFT JOIN checksheets cs ON cs.booking_id = b.id
    -- Find bookings that overlap with the target time range
    WHERE b.start <= @target_end 
      AND b.end >= @target_start
      -- Mark room as unavailable if:
      -- 1. Booking is Approved, OR
      -- 2. Booking is Completed but keys weren't returned before the target window starts
      AND (
          s.name = 'Approved'
          OR (
              s.name = 'Completed' 
              AND (cs.keys_returned_at IS NULL OR cs.keys_returned_at > @target_start)
          )
      )
);

Testing the Scenarios #

Let's verify this with your sample data:

Scenario 1: Only Booking 1 exists #

Booking 1 runs from 2018-01-15 08:00:00 to 2018-01-16 08:00:00, is marked Completed, and keys were returned at 2018-01-15 10:00:00 (before the target start time). This booking doesn't meet the "unavailable" criteria, so Room 1 is returned as expected.

Scenario 2: Booking 2 exists #

Booking 2 runs from 2018-01-15 11:00:00 to 2018-01-17 08:00:00 and is marked Approved. It overlaps with the target window, so Room 1 is added to the unavailable list and won't be returned—exactly what we want.

Extra Notes #

• Using LEFT JOIN checksheets handles cases where a Completed booking might not have a corresponding checksheet entry (we treat this as keys not being returned, so the room stays unavailable).
• DISTINCT ensures we don't mark a room as unavailable multiple times if it has multiple conflicting bookings.
• Using parameters @target_start and @target_end makes the query easier to maintain—just swap these out with your user's input values.

内容的提问来源于stack exchange，提问作者martincarlin87