How to migrate has_and_belongs_to_many to has_many through?

Question

We have a simple has_and_belongs_to_many relation between two models. We would like to add in some paramaters to that model, so we need to change it to a has_many :through sort of model.

As I know it, we need to add in an id column (along with whatever columns we want additionally). However, I'm not clear 100% on how to do this. If we add an integer column :id, will rails know that that is the 'id' primary key?

We're using the latest 3.x.

Answer 1

Assuming your database table for the has_and_belong_to many created by rails is patients_physicians

All you need to do is generate a model like this

rails g model patients_physician --skip-migration

then you can add whatever column you need with your migration commands like do

rails g migration add_new_column_to_patients_physician new_column

and your data will still be intact and you can do your query based ton the the model you generated.

Dont forget to add

belongs_to :model_1
belongs_to :model_2 

to the newly added model patients_physician

then you will have access to do in the model you need.

has_many patients_physician
has_many :model_2, through: :patients_physician

Answer 2

Just add id on your table in migration:

add_column :table, :id, :primary_key

referenced from this answer

Answer 3

There's a post here that illustrates using sql to patch the habtm table and add an id (as a pkey): Rails modeling: converting HABTM to has_many :through . I had trouble with that approach, and there may be db-specific issues. I ended up trashing the join (habtm) table and creating a new join model. That worked.

Some caveats: before doing this, I recommend creating a branch in git and archiving the db for an easy recovery if it goes sideways.

These were the steps:

1. Edit the two joined models to use has_many through

   class Physician < ActiveRecord::Base
     # has_and_belongs_to_many :patients
     has_many :appointments
     has_many :patients, :through => :appointments
   end
   

   Do the same for patients.

2. Create the new join model:

   rails g model Appointment physician_id:integer patient_id:integer has_insurance:boolean
   

   Edit the new migration file generated above... Note that 'change' as the migration method does not work, since we are processing data. See below.

3. create the new join model in the db and map all the old habtm associations to new has_many through records:

   def self.up
     create_table :appointments do |t|
       t.integer :physician_id
       t.integer :patient_id
       t.boolean :has_insurance
   
       t.timestamps
     end
   
     Physician.find_each {|doc|
       doc.patients.each do |pat|
         Appointment.create!(:physician_id => doc.id, :patient_id => pat.id, :has_insurance => false )
       end
     }
   
     # finally, dump the old hatbm associations
     drop_table :patients_physicians
   
   end
   

4. If it seems too much of a pain to reconstruct the old habtm associations, as per the rails guide, just abort. Note, however, that one can no longer rollback the migrations with this approach.

   def self.down
     raise ActiveRecord::IrreversibleMigration
   end
   

   Instead, to go 'down', just kill the git branch, and reload your backup db. Then you can resume db:rollback from there if necessary. On the other hand, if the has_many through records need no modification, another approach is to just drop the :id column, and rename the db:

   def self.down
     remove_column :appointments, :id
     rename_table :appointments, :patients_physicians
   end
   

   I have not test the latter (as in my case I do have to mess with the metadata). These ideas came from this post: http://7fff.com/2007/10/31/activerecord-migrating-habtm-to-model-table-suitable-for-has_many-through/.

Answer 4

This is the migration I used for converting a has_and_belongs_to_many relationship between Teams and Users:

class CreateTeamMembers < ActiveRecord::Migration
  def up
    # Create a new table with id and timestamps to replace the old one
    create_table :team_members do |t|
      t.belongs_to :team
      t.belongs_to :user
      t.timestamps
    end

    # Now populate it with a SQL one-liner!
    execute "insert into team_members(team_id,user_id) select team_id,user_id from teams_users"

    # drop the old table
    drop_table :teams_users
  end

  def down
    # This leaves the id and timestamps fields intact
    rename_table :team_members, :teams_users
  end
end