rails average between multiple models

Question

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

Answer 1

Nevermind! The answer was right in front of me, i just didn't get the proper association.

  def self.price
    Available.minimum(:price,
              :conditions => [ "price <> 0" ])
  end

This works perfectly after using

:has_many, :through => :model

I just didn't realize I had to setup a more complex relationship in order for it to work properly...

Answer 2

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