table aliases in a JOIN (was "discriminating two FK refs..")

Note: I've got SQL doing what I need. Now I just need ActiveRecord to produce the corresponding query. Consequently, this is a re-framing of the post   Discriminating two FK references into one table? - Rails - Ruby-Forum   'discriminating two FK references into one table?'

Here's an SQL query I'd like ActiveRecord to produce, without dropping down into raw SQL (model and schema are listed at the end of this post):

[code]SELECT sales_facts.asking_price, sales_facts.sale_price, listed.datetime AS listed_date, sold.datetime AS sold_date FROM sales_facts JOIN date_dimensions listed ON listed.id = sales_facts.listed_date_dimension_id JOIN date_dimensions sold ON sold.id = sales_facts.sold_date_dimension_id;[/code]

A few things to notice:

* There are two joins onto the same date_dimensions table. We're using table name aliases to distinguish the two ('listed' and 'sold').

* We're using column name aliases to distinguish the results in the SELECT ('AS listed_date' and "AS sold_date'). Without this, we wouldn't know which datetime came from the listed_date join and which came from the sold_date join.

So the real question is: what associations and embellishments do I add to my SalesFact ActiveRecord so I can make queries w/o dropping down into raw SQL? And what query would produce the SQL as written above?

The schema and models follow. Thanks.

- ff

Models: [code] class SalesFact < ActiveRecord::Base   belongs_to :address_dimension   belongs_to :listed_date_dimension, :class_name => 'DateDimension', :foreign_key => 'listed_date_dimension_id'   belongs_to :sold_date_dimension, :class_name => 'DateDimension', :foreign_key => 'sold_date_dimension_id' end class AddressDimension < ActiveRecord::Base   has_many :sales_facts end class DateDimension < ActiveRecord::Base   has_many :sales_facts end [/code] Schema: [code]   create_table "sales_facts", :id => false, :force => true do |t|     t.integer "address_dimension_id"     t.integer "listed_date_dimension_id"     t.integer "sold_date_dimension_id"     t.float "asking_price"     t.float "sale_price"   end   create_table "address_dimensions", :force => true do |t|     t.string "street_address"     t.string "zip5"     t.float "latitude"     t.float "longitude"     # snip...   end   create_table "date_dimensions", :force => true do |t|     t.datetime "datetime"     t.string "short_day_name"     t.string "short_month_name"     t.string "quarter"     t.boolean "is_weekend"     t.boolean "is_holiday"     # snip...   end [/code]

By the way, the following works, but I was hoping that ActiveRecord could assemble a query without so much exposed SQL. Does anyone have anything better?

s = SalesFact.all(   :select => 'sold.short_month_name AS sold_month, ' +              'COUNT(*) as units_sold',   :joins => 'INNER JOIN date_dimensions listed ON listed.id = sales_facts.listed_date_dimension_id ' +             'INNER JOIN date_dimensions sold ON sold.id = sales_facts.sold_date_dimension_id',   :group => 'sold.short_month_name',   :order => 'sold.month_of_year'   ) ; true

[For those watching at home, this counts how many sales closed in each month of the year, and returns the result ordered from Jan ... Dec.]