Forum: Ruby on Rails 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?