How to make a complex named scope play nice with associations and other named scopes [rails]

Question

I have the following named scope on

  class RentableItem < ActiveRecord::Base
  named_scope :available_at, lambda{ |starts_at, ends_at| { 
      :select => "t.*",
      :from => "(SELECT ri.*, COALESCE(c1.start_date, '#{starts_at}') AS EarliestAvailable,
                              COALESCE(c2.end_date, '#{ends_at}') AS LatestAvailable
                FROM rentable_items ri
                LEFT OUTER JOIN contracts c1 ON (ri.id = c1.rentable_item_id AND c1.start_date BETWEEN '#{starts_at}' AND '#{ends_at}')
                LEFT OUTER JOIN contracts c2 ON (ri.id = c2.rentable_item_id AND c2.end_date BETWEEN '#{starts_at}'
                AND '#{ends_at}' AND c2.start_date >= c1.end_date))
                AS t",
      :joins =>"LEFT OUTER JOIN contracts x ON (t.id = x.rentable_item_id AND x.start_date < t.LatestAvailable
                AND x.end_date > t.EarliestAvailable)",
      :conditions => "x.id IS NULL AND DATEDIFF(t.LatestAvailable, t.EarliestAvailable) >= #{(starts_at.to_date..ends_at.to_date).to_a.size - 1}"
      }}
end

The single call on this named_scope works like a charm, but when i'm trying to chain several named scopes together, or access this named_scope through an association-scope it blows up. I think, that the select statement and the custom from clause is the problem. Maybe someone has an idea how to rewrite this named_scope to allow chaining and scoping trough associations?

edit:

thanks to Shtééf it is working alomst.

  named_scope :available_at, lambda{ |starts_at, ends_at| { 
      :select => "rentable_items.*",
      :from => "(SELECT ri.*, COALESCE(c1.start_date, '#{starts_at}') AS EarliestAvailable,
                              COALESCE(c2.end_date, '#{ends_at}') AS LatestAvailable
                FROM rentable_items ri
                LEFT OUTER JOIN contracts c1 ON (ri.id = c1.rentable_item_id AND c1.start_date BETWEEN '#{starts_at}' AND '#{ends_at}')
                LEFT OUTER JOIN contracts c2 ON (ri.id = c2.rentable_item_id AND c2.end_date BETWEEN '#{starts_at}'
 AND '#{ends_at}' AND c2.start_date >= c1.end_date))
 AS rentable_items",
      :joins =>"LEFT OUTER JOIN contracts x ON (rentable_items.id = x.rentable_item_id AND x.start_date < rentable_items.LatestAvailable
AND x.end_date > rentable_items.EarliestAvailable)",
      :conditions => "x.id IS NULL AND DATEDIFF(rentable_items.LatestAvailable, rentable_items.EarliestAvailable) >= #{(starts_at.to_date..ends_at.to_date).to_a.size - 1}"
      }}

This allows me now to chain the scopes and access it through associations.

There is, maybe only a cosmetic, little problem with a doubled where condtion, which rails generates (at begin and end:

WHERE (`rentable_items`.container_item_id = 1) AND (((size > 10) AND (x.id IS NULL AND DATEDIFF(rentable_items.LatestAvailable, rentable_items.EarliestAvailable) >= 28)) AND (`rentable_items`.container_item_id = 1)) 

Answer 1

You should inspect the log files for your application and see what assumptions Rails is making that are causing the generated query to fail. If you can't fix it, show us an example association you're trying to access it through, and the query Rails generates from the log file.

My instinct here says that Rails assumes it can access the table rentable_items as just rentable_items, but instead, you have aliased it as ri in this situation.