discriminating two FK references into one table?

My question MIGHT be "ActiveRecord play nice with multiple views into a
table?

I ask because my application has a single DateDimension table (heavily
ornamented date objects). Each SalesFact has TWO foreign keys into the
DateDimension table: one for the date a house went on the market, one
for when it was sold [*].

My first model+schema is shown below, but I'm stymied how to construct a
query that joins the SalesFact table with the Dimension tables. More
specifically, how do you craft a :select that includes on_market_date
and a sale_date, since they're both foreign keys into the same
date_dimension table? Extra points if you can do it all with
associations and not drop down into SQL.

If that's not possible, I'm pretty sure I can create multiple views of
DateDimension to eliminate the ambiguity. But if there's a more RoR'ish
way to do this, I'm all ears.

# ===== the models...
class SalesFact < ActiveRecord::Base
  belongs_to :address_dimension
  belongs_to :on_market_date_dimension
  belongs_to :sale_date_dimension
end
class AddressDimension < ActiveRecord::Base
  has_many :sales_facts
end
class DateDimension < ActiveRecord::Base
end
class OnMarketDateDimension < DateDimension
  has_many :sales_facts
end
class SaleDateDimension < DateDimension
  has_many :sales_facts
end
# ===== and the schema...
  create_table "sales_facts", :id => false, :force => true do |t|
    t.integer "address_dimension_id"
    t.integer "on_market_date_dimension_id"
    t.integer "sale_date_dimension_id"
    t.float "asking_price"
    t.float "sale_price"
  end
  create_table "address_dimensions", :force => true do |t|
    t.string "house_number"
    t.string "street_name"
    ... lots and lots of other fields
    t.float "latitude"
    t.float "longitude"
    t.string "postal_code"
  end
  create_table "date_dimension", :force => true do |t|
    t.datetime "datetime"
    t.boolean "is_weekend"
    t.boolean "is_holiday"
    ... lots and lots of other fields
    t.string "day_name"
    t.string "month_name"
    t.integer "quarter"
  end
# =====

[*] NOTE: Many people say "why don't you just put the two dates directly
into the SalesFact table?" Rather than tilt at that windmill, I'll
simply offer two pointers on the topic:
   http://philip.greenspun.com/sql/data-warehousing.html (excellent
intro)
   http://www.kimballgroup.com/html/booksDWT2.html (from the man
himself)

Well first of all are you 100% sure you need OnMarketDateDimension and
SaleDateDimension classes? If you don't have any custom logic in them
you should probably remove them and change associations a bit:
class SalesFact < ActiveRecord::Base
  ...
  belongs_to :on_market_date_dimension, :class_name =>
'DateDimension', :foreign_key => 'on_market_date_dimension_id'
  belongs_to :sale_date_dimension, :class_name =>
'DateDimension', :foreign_key => 'sale_date_dimension_id'
end

And I see no reason why you can't just do: SalesFact.first :include =>
[:on_market_date_dimension, :sale_date_dimension]. Or did I
misinterpret what you need?

# ===== the models...
class SalesFact < ActiveRecord::Base
  belongs_to :address_dimension
  belongs_to :on_market_date_dimension
  belongs_to :market, :class_name => 'SaleDateDimension', :foreign_key
=> 'on_market_date_dimension_id'
  belongs_to :sale, :class_name => 'SaleDateDimension', :foreign_key
=> 'sale_date_dimension_id'
end

class SaleDateDimension < DateDimension
  ###has_many :sales_facts
  has_many :market, :class_name => 'SalesFact'
  has_many :sale, :class_name => 'SalesFact'
end

With that defined, in your views, etc., you can say something like

x = SalesFact.find(:id)
y = x.market.day_name

Hope this is what you're looking for.

Andrius Chamentauskas wrote:

Well first of all are you 100% sure you need OnMarketDateDimension and
SaleDateDimension classes? If you don't have any custom logic in them
you should probably remove them and change associations a bit:
class SalesFact < ActiveRecord::Base
  ...
  belongs_to :on_market_date_dimension, :class_name =>
'DateDimension', :foreign_key => 'on_market_date_dimension_id'
  belongs_to :sale_date_dimension, :class_name =>
'DateDimension', :foreign_key => 'sale_date_dimension_id'
end

And I see no reason why you can't just do: SalesFact.first :include =>
[:on_market_date_dimension, :sale_date_dimension]. Or did I
misinterpret what you need?

Andrius: I your solution is very close. I agree that it does not make
sense to create OnMarketDateDimension and SalesDateDimension classes (or
sql views onto a table, as I was suggesting). I've knocked together a
project implementing your approach, but I haven't grok'ed how to make a
query -- maybe you can help.

If I try this:

SalesFact.first(:include => [:on_market_date_dimension,
:sale_date_dimension])

I get an un-embellished SalesFact, which doesn't do me much good -- I'd
like join with the two dates to which it refers. If I try the same
thing with a :select:

SalesFact.first(:select => 'sales_facts.*, on_market_date_dimensions.*,
sale_date_dimensions.*', :include => [:on_market_date_dimension,
:sale_date_dimension])

I get an SQL error because Rails is generating a query based on
"sales_fact.id", which we intentionally suppressed in the schema (:id =>
false).

So my question: Given a SalesFact (or a group of SalesFacts), what's the
AR.find() syntax for a query that does a join against
:on_market_date_dimension and :sale_date_dimension?

Thanks in advance...

A bit more info after poking around. The following query doesn't work:

SalesFact.first(
  :select => 'sales_facts.asking_price,
on_market_date_dimensions.datetime, sale_date_dimensions.datetime,
address_dimensions.street_name',
  :joins => [:on_market_date_dimension, :sale_date_dimension,
:address_dimension])

(i.e. a three-way join on the three foreign keys of a SalesFact record,
and only selecting specific columns in the result).

Here's the SQL it generates, which fails because there is no
on_market_date_dimensions table:

SELECT sales_facts.asking_price, on_market_date_dimensions.datetime,
sale_date_dimensions.datetime, address_dimensions.street_name
FROM `sales_facts`
INNER JOIN `date_dimensions`
ON `date_dimensions`.id = `sales_facts`.on_market_date_dimension_id
INNER JOIN `date_dimensions` sale_date_dimensions_sales_facts
ON `sale_date_dimensions_sales_facts`.id =
`sales_facts`.sale_date_dimension_id
INNER JOIN `address_dimensions`
ON `address_dimensions`.id = `sales_facts`.address_dimension_id
LIMIT 1

I thought the point of @Andrius's approach was to give the model enough
info so that it would refer to the date_dimension table and not the
(non-existent) on_market_date_dimension table.

Just do SalesFact.first(:include =>
[:on_market_date_dimension, :sale_date_dimension]). Then you can
access dates with sales_fact.on_market_date_dimension and
sales_fact.sale_date_dimension.

Andrius Chamentauskas wrote:

Just do SalesFact.first(:include =>
[:on_market_date_dimension, :sale_date_dimension]). Then you can
access dates with sales_fact.on_market_date_dimension and
sales_fact.sale_date_dimension.

Ah - yes, that would work! But am I wrong in thinking that pushes all
the logic out of the DB and into Rails? For example, consider the
following query that shows only sales that closed in Q1:

SELECT sale.asking_price, sale.sale_price, listed.datetime AS
listed_date, sold.datetime AS sold_date
FROM sales_facts sale
JOIN date_dimensions listed ON listed.id =
sale.on_market_date_dimension_id
JOIN date_dimensions sold ON sold.id = sale.sale_date_dimension_id AND
sold.quarter = "Q1";

If I do an :include, I'm not sure how to keep the sold.quarter = "Q1"
within SQL. I'm probably missing something...

P.S.: I've re-framed this question in
http://www.ruby-forum.com/topic/205883 -- perhaps it's clearer there?