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.

1
2

class ViewModel < ActiveRecord::Base
end

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

class ViewModel < ActiveRecord::Base
  set_primary_key :unique_id # unique_id will be the name of a unique column used in the view
end

3) Create a new field in the view that can be used as the primary key.

1

create view view_model as select (field1_id::varchar + '-' + field2_id::varchar) as id, ...

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

versus

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

class ViewModel < ActiveRecord::Base
  set_primary_keys :field1_id, :field2_id
end

note
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.