In a recent project I was faced with generated SQL to query a star schema i.e. a fact table with surrounding dimension tables. For the sake of this post lets presume we have the following tables in PostgreSQL:
create table calendars ( id serial, calendar_date date ); create table locations ( id serial, name varchar(100) ); create table products ( id serial, name varchar(100) ); create table sales ( id serial, calendar_id integer, location_id integer, product_id integer, quantity integer );
and the following models setup:
class Calendar < ActiveRecord::Base has_many :sales, :foreign_key => "calendar_id" end class Location < ActiveRecord::Base has_many :sales, :foreign_key => "location_id" end class Product < ActiveRecord::Base has_many :sales, :foreign_key => "product_id" end class Sale < ActiveRecord::Base belongs_to :calendar, :foreign_key => "calendar_id" has_one :location, :foreign_key => "location_id" has_one :product, :foreign_key => "product_id" end
now you can query the star schema like so:
@sales = Sale.find( :all, :select => "calendars.calendar_date, locations.name, products.name, sales.quantity", :joins => [:location, :calendar, :product] )
which generates sql like so:
select calendars.calendar_date, locations.name, products.name, sales.quantity from "sales" inner join "locations" on locations.id = sales.location_id inner join "calendars" on calendars.id = sales.calendar_id inner join "products" on products.id = sales.product_id