客房预订系统查询

Question

我有以下关联

User:   
  has_many :reservations
  has_many :rooms, through: :reservations
Room:
  has_many :reservations
  has_many :users, through: :reservations
Reservation:
  belongs_to :room
  belongs_to :user

在我的预订模型中我有以下字段

checkin_date, checkout_date

我想查一下在给定时间内所有没有被预订的房间。

我已经写了以下查询，但它不工作。请提出更正或更好的方法。

    Room.joins('LEFT JOIN reservations ON reservations.room_id = rooms.id').where.not(
       'reservations.checkin_at < :requested_end_date AND 
        reservations.checkout_at > :requested_start_date', 
        requested_start_date: date_begin, 
        requested_end_date: date_end
    )    

Answer 1

解决方案

class Room < ActiveRecord::Base
  has_many :reservations
  has_many :users, through: :reservations
  def self.find_unreserved date_begin, date_end, category 
    reserved_room_ids = Reservation.on(date_begin, date_end).pluck('DISTINCT room_id')
    if reserved_room_ids.empty?
      where(category: category)
    else
      where('id NOT IN ?', reserved_room_ids).where(category: category)
    end
  end
end
class Reservation < ActiveRecord::Base
  belongs_to :room
  belongs_to :user

  scope :on, -> (checkin_date, checkout_date) {where('checkin_at > ? AND checkout_at < ?', checkin_date, checkout_date) }
end

Answer 2

也许…

Room.joins('left join reservations on reservations.room_id = rooms.id')
           .where('reservations.checkin_at > ? AND reservations.checkout_at < ?', date_begin, date_end)
           .where('reservations.room_id IS NULL')