CODEWITHSUNDEEP
sqlruby-on-railsactiverecordruby-on-rails-4database-migration
Michelle
Michelle

Reputation: 2712

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

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!

Upvotes: 4

Views: 4326

Answers (2)

Peter Goldstein
Peter Goldstein

Reputation: 4545

I wouldn't do the data migration in ActiveRecord. There are forward compatibility issues with directly referencing ActiveRecord model classes in migrations. As a general rule I try and avoid it. So I'd fall back to SQL.

Not sure what DB you're using, but something like this should work for MySQL:

execute("INSERT INTO cities(name, created_at, updated_at) SELECT DISTINCT city, NOW(), NOW() FROM buildings;")
execute("UPDATE buildings SET buildings.city_id = (SELECT city_id FROM cities where cities.name = buildings.name);")

The first statement initializes the cities table with no duplicates, while the second statement sets the city_id column on buildings.

Similar statements (potentially with slightly different syntax) will work for other databases.

Upvotes: 6

gotva
gotva

Reputation: 5998

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)

Upvotes: 6

Related Questions