Best way to migrate table data to other table

Question

Considering 4 models (very simplified)

class Group1 << AR::Base
  has_many group1_items
end
class Group2 << AR::Base
  has_many group2_items
end

class GroupItem << AR::Base
  belongs_to :group1
  belongs_to :thing
end
class Group2Item << AR::Base
  belongs_to :group2
  belongs_to :thing
end

I want to "merge" Group2 and Group2Items to Group1 and Group1Items. Group2 will inherit from Group1. What I want :

class Group2 << Group1

Group2Item model will be inused.

I need to create a migration to 'move' Group2 and Group2Items data to Group1 and Group1Item tables.

My migration must be accessible whatever the application state, i.e. the Group2 and Group2Item tables can not be present, so I need to do this juste in mySQL syntax.

Is there a simple way to do this ?

Answer 1

def up
  change_table :group1s do |t|
    t.string :type       # add a 'type' column for STI
    t.integer :old_id    # add a temporary column to hold the original 'id'
  end

  execute("UPDATE group1s SET `type` = 'Group1';")

  merge_items_sql = <<SQL
    INSERT INTO group1s (`type`, old_id, other_field_names...) 
    SELECT 'Group2', group2s.id, other_field_values...
    FROM group2s;

    INSERT INTO group1_items(group1_id, thing_id )
    SELECT group1s.id, group2_items.thing_id
    FROM group2_items
    JOIN group1s ON group2_items.group2_id = group1s.old_id;
SQL

  execute(merge_items_sql)

  # leave this out until you have verified that all data has merged properly
  drop_table :group2_items
  drop_table :group2s
  remove_column :group1s, :old_id
end