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

**URL:** <https://www.ruby-forum.com/t/table-aliases-in-a-join-was-discriminating-two-fk-refs/185016>\
**Category:** Rails\
**Created:** [March 11, 2010, 8:50pm UTC](https://www.ruby-forum.com/t/table-aliases-in-a-join-was-discriminating-two-fk-refs/185016 "2010-03-11T20:50:41Z")\
**Posts on this page:** 2\
**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 11, 2010, 8:50pm UTC](https://www.ruby-forum.com/t/table-aliases-in-a-join-was-discriminating-two-fk-refs/185016/1 "2010-03-11T20:50:41Z")

</div>

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](http://www.ruby-forum.com/topic/205747)  
‘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):

`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;`

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:

```auto
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
```

Schema:

```auto
  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
```

---

<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, 10:07pm UTC](https://www.ruby-forum.com/t/table-aliases-in-a-join-was-discriminating-two-fk-refs/185016/2 "2010-03-11T22:07:32Z")

</div>

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.]