Rails star schema query using vanilla active record

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s