Ruby on Rails: writing a migration to move a column to another table, merge duplicates, then match IDs

Question

I'm stuck on writing this migration.

Currently I have a buildings table. It lists the building's city as a column.

(There are duplicate cities names in the city column.)

  create_table "building", force: true do |t|
    t.string   "name"
    t.string   "city"
    t.datetime "created_at"
    t.datetime "updated_at"
  end

I have now created a city model, that has_many :buildings.

The buildings now belongs_to :city.

I have added a new column city_id to buildings for the belongs_to association.

  create_table "city", force: true do |t|
    t.string   "name"
    t.datetime "created_at"
    t.datetime "updated_at"
  end
  add_index "city", ["name"], name: "index_cities_on_name", unique: true

I am trying to write migrations that will do the following:

1. Copy the "city" column from the buildings table to the "name" column in the cities table.

2. Merge all duplicated cities in the cities name column

3. Match the "city_id" column in the buildings table to the correct city

But I am absolutely stuck... I've been flipping through the ActiveRecords documentations for an hour now and I'm still not quite sure what to do. Any help would be extremely appreciated! Thank you!

Answer 1

Note: as for me I am against writing SQL query to update data in tables in migrations. I prefer to use rake tasks for this. You can place code below where you would like.

1st. write (and run) a migration with cities table

2nd. perform this code in rake task or migration

Building.all.each do |building|
  city = City.find_or_create(name: building.city) # http://api.rubyonrails.org/classes/ActiveRecord/Relation.html#method-i-find_or_create_by
  building.city_id = city.id
  building.save!
end

3rd. write migration which remove column buildings.city

Some notes:

1. if you have a lot of buildings (for example 10000+ records) you can use find_each http://api.rubyonrails.org/classes/ActiveRecord/Batches.html#method-i-find_each
2. I prefer to use save! to be sure that I will notice that some building can not be saved - I can recheck this item manually
3. do not add reference to Building belongs_to :city before 2nd step, because I am not sure what column Rails will try to find buildings.city (old city name) or object city (it will nil because city_id is nill at this moment)