Rails Data Migration Involving Two Records

Question

So I understand that in general data migrations are not best practice, so I would be happy to implement a rake task instead.

We previously had two models, User and Office without a formal relationship. The office a user belonged to was stored as a string with just the office name. In order to look up an office we had to use:

Office.find_by_name(user.office)

This clearly makes no sense, so I'm trying to run a migration to fix this. I'm adding the field "office_id" to the user so that we can use a proper belongs_to/has_one relationship.

I want to ensure that every user's office is copied over into the new format, and so in this case I'm using a data migration in the middle of the schema migration. We must also remove the old "office" field as otherwise it interferes with the belongs_to method that is generated.

This is what I have:

if not column_exists? :users, :office_id
  add_column :users, :office_id, :integer

  if defined? User
    User.all.each do |user|
      results = ActiveRecord::Base.connection.execute("select office FROM users where id = #{user.id}")
      office = Office.find_by_name(results.getvalue(0,0))
      if office.present?
        user.office_id = office.id
        user.save!
      end
    end;nil
  end

  if column_exists? :users, :office
    remove_column :users, :office
  end
end

I had to use

ActiveRecord::Base.connection.execute("select office FROM users where id = #{user.id}")

Because the user.office was pulling from the belongs_to method generated, so this ensured we got the data from the database.

I saw this as one solution that would clean the code up a decent amount, except that I'm not sure how to pull the data from the office and set it to the user with sql.

Is the relation of User `belongs_to :office` mandatory? – MrYoshiji Mar 13 '18 at 19:45 — MrYoshiji, Mar 13 '18 at 19:45
It is incredibly useful, and cleans up a lot of code. – Joalro Mar 13 '18 at 19:58 — Joalro, Mar 13 '18 at 19:58

score 0 · Answer 1 · answered Mar 13 '18 at 20:00

Why you are not creating a relationship based on string column?

class User
  belongs_to :office, foreign_key: :office_name, primary_key: :name
end

To be able to do this, you should rename the office column to something else or if you don't want to rename it, you can pick a different name for your association and keep using the office column like so;

class User
  belongs_to :user_office, foreign_key: :office, primary_key: :name
end

If you want to migrate the data anyway, I recommend you to write a rake task to run only once like below;

task set_users_office_id: :environment do
  ActiveRecord::Base.connection.execute_sql(<<-SQL.squish)
    UPDATE users
    SET office_id = (SELECT id FROM offices WHERE offices.name = users.office)
  SQL
end

Rails Data Migration Involving Two Records

1 Answers1