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
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):
[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]
--
Posted via http://www.ruby-forum.com/.
--
You received this message because you are subscribed to the Google Groups "Ruby
on Rails: Talk" group.
To post to this group, send email to rubyonrails-t...@googlegroups.com.
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.
