Ruby on Rails finding records without a reservation

Question

I am making a hotel reservation system. I have models for User, Room, and Reservation. I have used associations to link as follows: (note, pseudo code)

User has_many Reservations
User has_many Rooms, through Reservations

Room has_many Reservations
Room has_many Users, through Reservations

Reservation belongs_to Users
Reservation belongs_to Reservations

All is working fine, for example I can list a user's reservations and rooms. Now I need to list all rooms which aren't currently reserved. I have tried the following which doesn't seem to work:

reservations = Reservation.where('start > ? AND end < ?', Time.now, Time.now).select('room_id')

if reservations.empty?
  @rooms = Room.all
else
  @rooms = Room.where('id not in (?)',
            reservations)
end

I wondered if there is an ActiveRecord method which would do this for me? I tried forcing a LEFT JOIN on rooms to get all rooms where the end date < now but that didn't work either.

Many thanks for your help!

I've got a messy work around: Loop through all of the rooms, if the room has reservations, loop through each of those and check their end date. It works but it's not pretty is it. Sure this is a simple left join SQL query really! — wkdshot
– wkdshot, Commented Dec 30, 2012 at 23:59

Yves Senn · Accepted Answer · 2012-12-30 23:47:54Z

1

What exactly did not work? I suggest you create an on scope to filter all the reservations, which are active on a given Date. Then you can use pluck to get an array of room ids to then fetch the Rooms themselves.

The code could look something like (warning, written from the top of my head, not tested):

class Reservation < ActiveRecord::Base
  scope :on, lambda {|day = Date.today| where('start > ? AND end < ?', day, day) }
end

class Room < ActiveRecord::Base
  def self.free_on(day = Date.today)
    reserved_room_ids = Reservation.on(day).pluck('DISTINCT room_id')
    where('id NOT IN (?)', reserved_room_ids)
  end

  def self.reserved_on(day = Date.today)
    reserved_room_ids = Reservation.on(day).pluck('DISTINCT room_id')
    where(:id => reserved_room_ids)
  end
end

Now you can use Room.free_on or Room.free_on(1.day.ago) to get the free rooms for a given day. I also added the method to get to the reserved rooms.

answered Dec 30, 2012 at 23:47

Yves Senn

1,99616 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

jxpx777 Over a year ago

This is also a good answer. Named scopes are way under-utilized. Also, kudos for introducing me to the pluck method. Hadn't seen that before. :)

jxpx777 · Accepted Answer · 2012-12-30 23:46:08Z

0

There is the exists? method. It will allow you to see if there are any objects that match the query.

answered Dec 30, 2012 at 23:46

jxpx777

3,6414 gold badges30 silver badges43 bronze badges

Collectives™ on Stack Overflow

Ruby on Rails finding records without a reservation

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related