Rails has_and_belongs_to_many migration

Question

I have two models restaurant and user that I want to perform a has_and_belongs_to_many relationship.

I have already gone into the model files and added the has_and_belongs_to_many :restaurants and has_and_belongs_to_many :users

I assume at this point I should be able to do something like with Rails 3:

rails generate migration ....

but everything I have tried seems to fail. I'm sure this is something really simple I'm new to rails so I'm still learning.

Boehmite

Answer 1

You need to add a separate join table with only a restaurant_id and user_id (no primary key), in alphabetical order.

First run your migrations, then edit the generated migration file.

Rails 3

rails g migration create_restaurants_users_table

Rails 4:

rails g migration create_restaurants_users

Rails 5

rails g migration CreateJoinTableRestaurantUser restaurants users

From the docs:

There is also a generator which will produce join tables if JoinTable is part of the name:

---

Your migration file (note the :id => false; it's what prevents the creation of a primary key):

Rails 3

class CreateRestaurantsUsers < ActiveRecord::Migration
  def self.up
    create_table :restaurants_users, :id => false do |t|
        t.references :restaurant
        t.references :user
    end
    add_index :restaurants_users, [:restaurant_id, :user_id]
    add_index :restaurants_users, :user_id
  end

  def self.down
    drop_table :restaurants_users
  end
end

Rails 4

class CreateRestaurantsUsers < ActiveRecord::Migration
  def change
    create_table :restaurants_users, id: false do |t|
      t.belongs_to :restaurant
      t.belongs_to :user
    end
  end
end

t.belongs_to will automatically create the necessary indices. def change will auto detect a forward or rollback migration, no need for up/down.

Rails 5

create_join_table :restaurants, :users do |t|
  t.index [:restaurant_id, :user_id]
end

Note: There is also an option for a custom table name that can be passed as a parameter to create_join_table called table_name. From the docs

By default, the name of the join table comes from the union of the first two arguments provided to create_join_table, in alphabetical order. To customize the name of the table, provide a :table_name option:

Aardvark

thanks that solution worked perfectly for me. Made a simple mistake when using this method, I tried a rake db:migrate after the rails command and it didn't generate the table. Had to do a rake db:rollback then edit the file then do rake db:migrate but everything works now. – Boehmite

@Aardvark - Just out of curiosity, could you explain why you're using a second compound index, defined with reversed column order? I was under the impression that the column order didn't matter. I'm no DBA, just want to further my own understanding. Thanks! – Lawlor

@Jimbo You don't need it that way, it really depends on your queries. The indexes read left to right so the first one will be fastest if you are searching on restaurant_id. The second will help if you are searching on user_id. If you are searching on both, I would think the database would be smart enough to only need one. So I guess the second one doesn't really need to be compounded. This was more of just an example. This was a Rails question though, so posting in the DB section would yield a more complete answer. – Aardvark

The second index has some redundancy - as long as you are querying on both restaurant_id and user_id, their order in your SQL doesn't matter, and the first index will be used. It will also be used if you are only querying on restaurant_id. The second index only needs to be on :user_id, and would get used in cases where you are only querying on user_id (which the first index wouldn't help with due to the order of its keys). – Flosi

Thanks for the solution-- I wanted to say there was one confusing point. When you reference the ":user" the rails systems seems to automatically reference the "user_id" field. I was a bit hung up on that for a bit. – Bootie

@Corey Yes, there's a lot of built in stuff that relies on naming conventions in Rails – Aardvark

In rails 4 the migration must be rails g migration create_restaurants_users without table at the end. – Topgallant

You could also use rails g migration CreateJoinTableRestaurantUser restaurant user. Read guides.rubyonrails.org/migrations.html#creating-a-join-table – Caaba

You also want to remove the index in the self.down call probably – Dastardly

What is the purpose of indexing the user_id alone? Why isn't this done for just the restaurant_id as well? – Guenzi

The first index (on both columns) most of the time should be unique. – Wyant

Worth noting that create_join_table can be used at least in Rails >= 4.2 – Corporeal

Answer 2

The answers here are quite dated. As of Rails 4.0.2, your migrations make use of create_join_table.

To create the migration, run:

rails g migration CreateJoinTableRestaurantsUsers restaurant user

This will generate the following:

class CreateJoinTableRestaurantsUsers < ActiveRecord::Migration
  def change
    create_join_table :restaurants, :users do |t|
      # t.index [:restaurant_id, :user_id]
      # t.index [:user_id, :restaurant_id]
    end
  end
end

If you want to index these columns, uncomment the respective lines and you're good to go!

Increase

I generally would uncomment one of the index lines and add unique: true to it. This will prevent duplicate relationships being created. – Coddle

Answer 3

When creating the join table, pay careful attention to the requirement that the two tables need to be listed in alphabetical order in the migration name/class. This can easily bite you if your model names are similar, e.g. "abc" and "abb". If you were to run

rails g migration create_abc_abb_table

Your relations will not work as expected. You must use

rails g migration create_abb_abc_table

instead.

Maggard

Which comes first? foo or foo_bar? – Settling

Ran in a rails console: ["foo_bar", "foo", "foo bar"].sort # => ["foo", "foo bar", "foo_bar"] Unix sort comes up the same. – Decrypt

Answer 4

For HABTM relationships, you need to create a join table. There is only join table and that table should not have an id column. Try this migration.

def self.up
  create_table :restaurants_users, :id => false do |t|
    t.integer :restaurant_id
    t.integer :user_id
  end
end

def self.down
  drop_table :restaurants_users
end

You must check this relationship rails guide tutorials

Rhondarhondda

I don't think you need a model and I don't see anything in the link about needing a model for a HABTM relationship. – Settling

To speed up the generated queries, add indices to the id fields. – Ritaritardando