Here’s a quick tip for anybody using a database view as an ActiveRecord model…
If you want to use your view as a model and take advantage of the association methods properly (like belongs_to, etc.), you must make sure that your view specifies a unique column that can be used as the primary key. Without a primary key for the view, you’ll likely run into some nasty bugs down the road (mine occurred when I tried to use eager loading via :include with the “view model”).
First off, to use the view as a model, you don’t need to do anything different than what you’d do for a regular table.
Here are some ways to set your primary key for your view model:
1) If your view already has a unique “id” field, then you’re done. No need to do anything else.
2) If your view has a unique field, but it’s not called “id”, simply use the set_primary_key method.
1 2 3
3) Create a new field in the view that can be used as the primary key.
The above SQL example uses two fields combined into one to form a unique field called “id” and this will be picked up automatically by ActiveRecord. The reason for adding the ‘-’ separator is so that 2 combinations of 2 separate values don’t combine to form the same value.
With no separator:
field1_id = 10, field2_id = 10, id = 1010 and field1_id = 101, field2_id = 0, id = 1010
With a separator:
field1_id = 10, field2_id = 10, id = 10-10 and field1_id = 101, field2_id = 0, id = 101-0
To those who know their databases, you’ll recognize this as basically a composite key and since RoR doesn’t support composite keys out of the box, this can be used to mimic one.
4) Use a composite key plugin like http://compositekeys.rubyforge.org to add composite key support to RoR
Now you can use composite keys instead of the method of generating one as in item 3.
1 2 3
Pretty much everything here applies to a regular model based off a table, but when creating a view, it’s more common to not include a column that will be unique for each record like the “id” field used in almost all tables used with RoR.