活动记录获取所有不符合条件的实例

Question

我有以下AR查询，它返回一个房间数组（在给定时间段内不可用的房间）：

rooms = Room.joins(:bookings).where("(bookings.start_date >= ? AND bookings.start_date <= ?) OR (bookings.end_date >= ? AND bookings.end_date <= ?) OR (bookings.start_date <= ? AND bookings.end_date >= ?)", from, to, from, to, from, to)

我想修改该查询，以便它返回所有其他房间;即在请求的时间段内可用的房间。正确的结果由以下公式给出：

all_rooms = Room.all
available_rooms = all_rooms - rooms

但我直接想在单个查询中获取可用房间。我已经添加了一个 .not，但它没有给我正确的结果：

rooms = Room.joins(:bookings).where.not("(bookings.start_date >= ? AND bookings.start_date <= ?) OR (bookings.end_date >= ? AND bookings.end_date <= ?) OR (bookings.start_date <= ? AND bookings.end_date >= ?)", from, to, from, to, from, to)

我应该如何修改查询？

Answer 1

假设：

A = (bookings.start_date >= ? AND bookings.start_date <= ?)
B = (bookings.end_date >= ? AND bookings.end_date <= ?) 
C = (bookings.start_date <= ? AND bookings.end_date >= ?)
first_logic  = A or B or C
second_logic = not(first_logic) = not(A or B or C) = not(A) and not(B) and not(C)

available_rooms是second_logic的条件，所以它会像：

available_rooms = Room.joins(:bookings)
                      .where.not("bookings.start_date >= ? AND bookings.start_date <= ?", from, to)
                      .where.not("bookings.end_date >= ? AND bookings.end_date <= ?", from, to)
                      .where.not("bookings.start_date <= ? AND bookings.end_date >= ?", from, to)