I've been trying to get my head around doing things using my Rails relationships as opposed to pulling out large SQL joins, but I can't wrap my head around this one...
I have 3 models
Hotels Rooms Availables
They all have the appropriate has_many and belongs_to relationships.
What I want to do is on the overview page listing the Hotels of a particular City, i want to list the lowest price found for each hotel.
Now in SQL I would of course do the bit of code at the bottom, but in rails I could do something like this...
def self.price
Available.minimum(:price,
:conditions => [ "price <> 0" ])
end
This of course just pulls the lowest price of all of them, not a specific ID
The problem is the relationship Hotel.find(1234).rooms.availables
But I'd like to do something like this that could go inside my loop without having to reference the ID?
SELECT MIN(availables.price)
FROM availables
INNER JOIN rooms ON rooms.id = availables.room_id
INNER JOIN hotels ON hotels.id = rooms.hotel_id
WHERE hotels.id = 5077 and rooms.private = 0 and availables.price <> 0
You can accomplish this by setting up a has_many :through relationship on Hotel:
class Hotel < ActiveRecord::Base
has_many :rooms
has_many :availables, :through => :rooms
# If you want "rooms"."private" condition, use these...
has_many :public_rooms, :class_name => 'Room', :conditions => {:private => 0}
has_many :public_availables, :through => :public_rooms, :source => :availables
# This caches the value (potentially saving you db hits) for the
# lifetime of the object, which you may or may not want depending
# on your needs...
def cheapest_available
@cheapest_available ||= availables.minimum(:price, :conditions => ['availables.price > ?', 0])
end
end
Now you can loop through all of the hotels in a particular city displaying the lowest price:
@city.hotels.each do |hotel|
puts "#{hotel.name}: #{hotel.cheapest_available}"
end