How to make a complex named scope playing nice with associations and other named scopes [rails]
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?
How to make a complex named scope playing nice with associations and other named scopes [rails]
===

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?