Add a Rails migration database column and populate it based on another column

Question

I am writing a transition to add a column to a table. The value of a column depends on the value of two more existing columns. What is the best / fastest way to do this? I currently have this, but am not sure if this is the best way, since the group table can be very large.

class AddColorToGroup < ActiveRecord::Migration def self.up add_column :groups, :color, :string Groups = Group.all.each do |g| c = "red" if g.is_active && is_live c = "green" if g.is_active c = "orange" g.update_attribute(:type, c) end end def self.down end end 

Answer 1

This is usually a bad idea to reference your models from your migrations. The problem is that the migrations are performed in order and change the state of the database as they arrive, but your models are not versioned at all. There is no guarantee that the model, as it existed when recording the migration, will still be compatible with the migration code in the future.

For example, if you change the behavior of the is_active or is_live in the future, this migration may break. This older migration will be launched first, against the new model code and may fail. In your base example, this may not occur, but it burned me down in the deployment before the fields were added and the checks could not be performed (I know that your code skips the checks, but in general this is a concern).

My favorite solution for this is to do all migrations of this kind using plain SQL. It sounds like you already thought that, so I'm going to assume that you already know what to do there.

Another option, if you have hairy business logic or just want the code to look more Railsy, ​​should include the basic version of the model as it exists when the migration is written to the migration file itself. For example, you can put this class in a migration file:

 class Group < ActiveRecord::Base end 

In your case, this is probably enough to ensure that the model does not break. Assuming that active and live are Boolean fields in the table at this time (and therefore, this will be whenever this migration starts in the future), you will no longer need the code. If you had more complex business logic, you can include it in this version of the migration-centric model.

You may even consider copying all the methods from your model into the migration version. If you do this, keep in mind that you should also not reference any external models or libraries of your application, if there is a possibility that they will change in the future. This includes gems and maybe even some of the core Ruby / Rails classes, because changes to gems are a violation of the APIs (I'm looking at you, Rails 3.0, 3.1, and 3.2!).

Answer 2

I would suggest making three general queries. Always use a database compared to loops over many elements in an array. I would have thought something like this might work.

To write this down, I assume that is_active checks the active field, where 1 is active. I guess life is the same.

Rails 3 Approach

 class AddColorToGroup < ActiveRecord::Migration def self.up add_column :groups, :color, :string Group.where(active: 1, live: 1).update_all(type: "red") Group.where(active: 1, live: 0).update_all(type: "green") Group.where(active: 0, live: 0).update_all(type: "orange") end end 

Feel free to check out the update_all documentation here .

Rails 2.x approach

 class AddColorToGroup < ActiveRecord::Migration def self.up add_column :groups, :color, :string Group.update_all("type = red", "active = 1 AND live = 1") Group.update_all("type = red", "active = 1 AND live = 0") Group.update_all("type = red", "active = 0 AND live = 0") end end 

Rails 2 Documentation

Answer 3

I would do it in

 after_create # or after_save 

in your ActiveRecord model:

 class Group < ActiveRecord::Base attr_accessor :color after_create :add_color private def add_color self.color = #the color (wherever you get it from) end end 

or during the migration process, you probably have to do this SQL:

 execute('update groups set color = <another column>') 

Here is an example in the Rails manuals:

http://guides.rubyonrails.org/migrations.html#using-the-up-down-methods

Answer 4

In a similar situation, I ended up adding a column using add_column and then using direct SQL to update the column value. I used direct SQL, and not the model for Jim Stewart's answer , since then it does not depend on the current state of the model and the current state of the table when starting the migration.

 class AddColorToGroup < ActiveRecord::Migration def up add_column :groups, :color, :string execute "update groups set color = case when is_active and is_live then 'red' when is_active then 'green' else 'orange' end" end def down remove_column :groups, :color end end