Join table with composite key in rails

Question

Let's assume a generic join table FollowCompany between a User and a Company model, e.g.:

class FollowCompany < ActiveRecord::Base
  attr_accessible :user_id, :company_id

  belongs_to :user
  belongs_to :company

end

It has a composite primary key [user_id, company_id]. It does NOT have an id column since a follow_company record is uniquely identified by [used_id, company_id]

Since I want to be able to treat the following relationship as a resource I made it RESTful:

routes.rb:

resources :follow_companies

This however causes a problem: These generated routes assume an :id key which I don't have. How do I tell rails that I'm actually using a composite key instead?

I can think of four solutions and I would like some input on which one is the best:

Don't make :follow_companies a resource. Instead, match the URLs with a two key pattern: e.g.: match '/follow_companies/:user_id/:company_id/' => follow_companies#edit This however is ugly because it's verbose and not RESTful.
Override the FollowCompany to_param method to contain both model ids, e.g.
```
def to_param
 "#{user_id},#{company_id}"
end
```
This however is ugly because it seems like a hack and it has some nasty side effects
Add a primary key column to the follow_company table. This however is ugly because it adds redundancy. follow_company records are uniquely identified by [company_id, user_id]. No need for an additional key.
Download a composite-key gem and integrate it with my dev environment. This however is ugly because it's not a standard way and not compatible with other ruby code.

So, as you can see I couldn't come up with an elegant solution. This seems like such a common scenario though that I can't be the first one to run into this. Virtually every app makes use of (restful) join tables. What's a best practice of handling this?

score 2 · Answer 1 · answered Mar 22 '13 at 22:12

2

Number 3 has the advantage that your key would then be non-meaningful. While not super applicable to your case, if one of the key values needed to change for some reason, using a composite key would cause the URL to that resource to change. Creating a non-meaningful primary key means that URL would stay the same even after the data changes.

This also tends to be the "Rails way" of doing join tables.

answered Mar 22 '13 at 22:12

1

+1 Yes and there are also many other good reasons to have a primary key that has no business value. More info at http://stackoverflow.com/a/8777574/631619 – Michael Durrant Mar 22 '13 at 22:16
OK, granted. But doesn't that denormalize the table by introducing redundancy? Now I have to worry about duplicates such as a user following the same company twice. It's even possible to insert a (id,NULL,NULL) record indicating that nobody is following no company. To prevent nonsense like this I would have to use constraints. To me it seems that the additional id column adds a whole lot of complexity and headaches instead of making things easier. Is this really the preferred way? – noone__ Mar 22 '13 at 23:05
1

Yes you could have (id, NULL, NULL) or duplicated, but surely you can validate for these things with validates_presence and validated_uniqueness :id1, scope: :id2 – JamesMacLeod Mar 23 '13 at 10:01
Where does ` validated_uniqueness :id1, scope: :id2` go when there is no model for the join table? Also, there doesn't seem to be any method named `validated_uniqueness` in the rails api. – ahnbizcad Apr 26 '14 at 08:30
1

@gwho `validates_uniqueness` is the Rails 2 syntax I believe. Checkout http://guides.rubyonrails.org/active_record_validations.html#uniqueness for the current way to do that. – Apr 27 '14 at 16:12

score 0 · Answer 2 · answered Mar 22 '13 at 22:02

I am not that confident with ActiveRecord but why have a combined key? Surely what you are describing is a many_to_many join. So would this not work:

class User < ActiveRecord::Base
  has_many :follow_companies
  has_many :companies, through: :follow_companies
end

class FollowCompany < ActiveRecord::Base
  belongs_to :user
  belongs_to :company
end

class Company < ActiveRecord::Base
  has_many :follow_companies
end

This means you could then route directly to the companies as a nested route like this:

resources :users do
  collection :follow_companies
end

This would give you routes like '/users/1/follow_companies' and in your controller your could do:

class UsersController < ApplicationController
  def follow_companies
    @user = User.find(params[:id])
    @companies = @user.companies
  end
end

Or something like that?

Join table with composite key in rails

2 Answers2