aggregate conditional has_many rails association

Question

I'm not sure if this is feasible or even preferable

But I'd like to build a has_many relationship or a conditional named_scope type relationship to ease my pain into one simple relationship instead of two.

Currently I have two models which basically contain the same information and belong to the same Model rooms thru the same key room_id, but hold data in two different ways. One contains specific information for a particular date and the second generic rates for days of the week. The main obstacle is that Availables may contain no information for a particular record at all, in which case it needs to defer to room_rates for average rather than specific prices for a given date.

Any pointers or suggestions? Thanks

Availables

+--------+-------+-------+------------+---------+-----+
| id     | price | spots | bookdate   | room_id | min |
+--------+-------+-------+------------+---------+-----+
| 180315 | 14.4  | 1     | 2010-02-27 | 2517    | 1   |
| 231726 | 15.84 | 1     | 2010-03-24 | 2517    | 1   |
| 180311 | 14.4  | 1     | 2010-02-23 | 2517    | 1   |
| 180313 | 14.4  | 1     | 2010-02-25 | 2517    | 1   |
+--------+-------+-------+------------+---------+-----+

Room_rates

+-------+---------+-----------+-------+-----+
| id    | room_id | dayofweek | price | min |
+-------+---------+-----------+-------+-----+
| 87936 | 2517    | 0         | 14.58 | 1   |
| 87937 | 2517    | 1         | 14.58 | 1   |
| 87938 | 2517    | 2         | 14.52 | 1   |
| 87939 | 2517    | 3         | 14.52 | 1   |
| 87940 | 2517    | 4         | 14.52 | 1   |
| 87941 | 2517    | 5         | 14.4  | 1   |
| 87942 | 2517    | 6         | 14.63 | 1   |
+-------+---------+-----------+-------+-----+

Or possibly a finder_sql method? Such as:

  has_many :aggregates,
           :class_name => 'Available',
           :finder_sql => 'SELECT room_id, price, spots, bookdate, MIN(source) 
           FROM availables
           WHERE room_id = #{id}
           GROUP BY room_id, price, spots, bookdate'

But it also needs to fill in missing records for bookdates that don't exist by joining room_rates

Answer 1

I would just add method to Room model. I supose you want to get price for a specific day.

def get_price(date)
  (self.availbles.find_by_bookdate(date) || self.room_rates.find_by_dayofweek(dayofweek(date)) ).price
end

You need to replace dayofweek(date) with something that will give you specific day of week. This will return price from availbles and if there is no such row, then from room_rates. If both rows are missing you will have error. I hope it will give you some idea how to solve this problem.

In case of a range of dates I would add another method:

def get_prices(start_date, end_date)
  dates = (start_date..end_date).to_a
  prices = {}
  dates.each do |d|
    prices[:d] = get_price(d)
  end
  prices
end

It will return hash with prices where date is a key

Answer 2

You can do this in one sql. But it requires you to execute a fairly big SQL. On the other hand you get the results in one statement with sorting and other goodies.

Disclaimer 1: This code is farm fresh, air coded and not subjected any harmful testing.

Disclaimer 2: You have 2 rows for same room on date 2010-02-25 in availables table. I am assuming that is an aberration. Otherwise you need to pass an additional parameter to the select statement to filter the results further.

class Available < ActiveRecord::Base  
  def self.daily_rates room_id, from_date, to_date 
    date_table = (from_date..to_date).collect do |date| 
      "(SELECT  #{room_id} AS room_id, '#{date.to_formatted_s(:db)}' AS bookdate )"
    end.join(" UNION ")

    ActiveSupport::OrderedHash.new.tap do |hash|
      Available.find_by_sql( " 
         SELECT A.bookdate, IFNULL(B.price, C.price) AS price
         FROM   (#{date_table}) A
         LEFT OUTER JOIN availables B 
                ON A.room_id = B.room_id AND A.bookdate = B.bookdate
         JOIN room_rates C 
                ON A.room_id = C.room_id AND DAYOFWEEK(A.bookdate) = C.dayofweek + 1
         ORDER BY A.bookdate ASC"
      ).each do |rate_info |
        hash[rate_info.bookdate] = rate_info.price
      end
    end
  end  

end

Now you can make calls such as:

rates = Available.daily_rates(1, '2010-02-23'.to_date, '2010-02-27'.to_date)

rates.keys[0]   # Tue, 23 Feb 2010
rates.values[0] # 14.3999996185303

Lets check the rate for the 26th which is missing from availables table.

rates.keys[3]   # Tue, 26 Feb 2010
rates.values[3] # 14.3999996185303