Rails has_and_belongs_to_many

I had a pretty zealous database design teacher pounded into us at school that if many to many relationships are coming up a lot in your design, then its probably a bad design. That still seems like solid advice to me but there are definitely times when its totally legit to use them. Tags, categories, roles. That sort of thing. Its been a while since I did one and I stumbled through doing one just now. Because of that I thought I would write it up for next time I need to have my memory jogged but also because I remember struggling with relations when I started doing Rails. So here goes.

I have an “Account” model and a “Role” model and want an account to have a habtm relation with roles. I started with this in my migration (which turned out to be wrong):

create_table :accounts_roles do |t|
t.integer :account_id
t.integer :role_id

Which produces this in the database:

mysql> desc accounts_roles;
| Field      | Type    | Null | Key | Default | Extra          |
| id         | int(11) | NO   | PRI | NULL    | auto_increment |
| account_id | int(11) | YES  |     | NULL    |                |
| role_id    | int(11) | YES  |     | NULL    |                |
3 rows in set (0.00 sec)

But the moment you try to save any associate a role with an account I got this:
ActiveRecord::ConfigurationError: Primary key is not allowed in a has_and_belongs_to_many join table (accounts_roles).
Which is actually one of the more helpful error messages I have seen, so thank you to whomever created it. A quick “rake db:rollback” and modify that migration:

create_table :accounts_roles, :id => false do |t|
t.integer :account_id
t.integer :role_id

“rake db:migrate” again and there we go. Now this is looking better:

mysql> desc accounts_roles;
| Field      | Type    | Null | Key | Default | Extra |
| account_id | int(11) | YES  |     | NULL    |       |
| role_id    | int(11) | YES  |     | NULL    |       |
2 rows in set (0.00 sec)

And don’t forget to add the relation to your models:

class Role < ActiveRecord::Base
has_and_belongs_to_many :accounts

class Account < ActiveRecord::Base
has_and_belongs_to_many :roles

Then you can save stuff in there using something like this:

>> me.roles.create :name => ‘admin’
=> #<Role id: 1, name: “admin”, created_at: “2010-07-02 20:31:01”, updated_at: “2010-07-02 20:31:01”>

Note that create implicitly saves the record so it already has a role id. The << operator works the same way so this would be equivalent:

me.roles<<Role.new(:name => ‘commenter’)

Build will add a new role object in there but won’t save it automatically so there won’t be a role id assigned until it is saved.

>> me.roles.build
=> #<Role id: nil, name: nil, created_at: nil, updated_at: nil>

That’s it!


7 thoughts on “Rails has_and_belongs_to_many”

  1. I think you need a comma after ‘:accounts_roles’ in your migration. I added that and then it worked for me.

    Thanks for posting what you found Helped me out a lot.

  2. Yup,
    As Rob said, there needs to be a comma after account_roles
    create_table :accounts_roles, :id => false do |t|

    Thanks for the post.

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 )

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s