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
