# Discriminating two FK references into one table?

**URL:** <https://www.ruby-forum.com/t/discriminating-two-fk-references-into-one-table/184889>\
**Category:** Rails\
**Created:** [March 10, 2010, 2:59am UTC](https://www.ruby-forum.com/t/discriminating-two-fk-references-into-one-table/184889 "2010-03-10T02:59:36Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![fearless\_fool](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/f/5e9695/32.png) [@fearless\_fool](https://www.ruby-forum.com/u/fearless_fool)\
**Post date:** [March 10, 2010, 2:59am UTC](https://www.ruby-forum.com/t/discriminating-two-fk-references-into-one-table/184889/1 "2010-03-10T02:59:36Z")

</div>

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:  
[Data Warehousing](http://philip.greenspun.com/sql/data-warehousing.html) (excellent  
intro)  
[The Data Warehouse Toolkit, 3rd Edition - Kimball Group](http://www.kimballgroup.com/html/booksDWT2.html) (from the man  
himself)

---

<div class="post-metadata">

**Author:** ![fearless\_fool](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/f/5e9695/32.png) [@fearless\_fool](https://www.ruby-forum.com/u/fearless_fool)\
**Post date:** [March 10, 2010, 11:05pm UTC](https://www.ruby-forum.com/t/discriminating-two-fk-references-into-one-table/184889/2 "2010-03-10T23:05:19Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![fearless\_fool](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/f/5e9695/32.png) [@fearless\_fool](https://www.ruby-forum.com/u/fearless_fool)\
**Post date:** [March 11, 2010, 12:32am UTC](https://www.ruby-forum.com/t/discriminating-two-fk-references-into-one-table/184889/3 "2010-03-11T00:32:04Z")

</div>

# ===== 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.

---

<div class="post-metadata">

**Author:** ![fearless\_fool](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/f/5e9695/32.png) [@fearless\_fool](https://www.ruby-forum.com/u/fearless_fool)\
**Post date:** [March 11, 2010, 5:54am UTC](https://www.ruby-forum.com/t/discriminating-two-fk-references-into-one-table/184889/4 "2010-03-11T05:54:21Z")

</div>

Andrius C. 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…

---

<div class="post-metadata">

**Author:** ![fearless\_fool](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/f/5e9695/32.png) [@fearless\_fool](https://www.ruby-forum.com/u/fearless_fool)\
**Post date:** [March 11, 2010, 6:52am UTC](https://www.ruby-forum.com/t/discriminating-two-fk-references-into-one-table/184889/5 "2010-03-11T06:52:01Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![fearless\_fool](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/f/5e9695/32.png) [@fearless\_fool](https://www.ruby-forum.com/u/fearless_fool)\
**Post date:** [March 11, 2010, 8:54pm UTC](https://www.ruby-forum.com/t/discriminating-two-fk-references-into-one-table/184889/6 "2010-03-11T20:54:19Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![fearless\_fool](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/f/5e9695/32.png) [@fearless\_fool](https://www.ruby-forum.com/u/fearless_fool)\
**Post date:** [March 11, 2010, 9:08pm UTC](https://www.ruby-forum.com/t/discriminating-two-fk-references-into-one-table/184889/7 "2010-03-11T21:08:36Z")

</div>

Andrius C. 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  
[Table aliases in a JOIN (was "discriminating two FK refs..") - Rails - Ruby-Forum](http://www.ruby-forum.com/topic/205883) – perhaps it’s clearer there?