Add Single Table Inheritance (STI) to existing models

Question

I currently have multiple tables that are very similar. I should probably have created them with STI.

TypeOne < ActiveRecord::Base
TypeTwo < ActiveRecord::Base
TypeThree < ActiveRecord::Base

TypeOne(id: integer, parent_id: integer, created_at: datetime, updated_at: datetime)
TypeTwo(id: integer, parent_id: integer, created_at: datetime, updated_at: datetime)
TypeThree(id: integer, parent_id: integer, created_at: datetime, updated_at: datetime)

I am now trying to add STI to these. I created a BaseModel and added a type to that model.

BaseModel(id: integer, parent_id: integer, created_at: datetime, updated_at: datetime, type: string)

I also ran a migration and added to all of the types, a type column.

class AddTypeToTables < ActiveRecord::Migration
  def change
    add_column :type_ones, :type, :string
    add_column :type_twos, :type, :string
    add_column :type_threes, :type, :string
  end
end

I want to combine all of the type tables into a STI. There is existing data in the models. If I were to combine them into a single table, I imagine the id's for the respective tables would conflict. For example:

#<TypeOne id: 4, parent_id: 1, created_at: "2015-05-08 18:39:09", updated_at: "2015-09-07 19:42:03">

#<TypeTwo id: 4, parent_id: 1, created_at: "2015-04-08 17:48:59", updated_at: "2015-09-07 14:17:48">

If I try to use becomes, it appears to change the class, but I cannot find the record in the BaseModel

TypeOne.last.becomes!(BaseModel)
#<BaseModel id: 4, parent_id: 1, created_at: "2015-05-08 18:39:09", updated_at: "2015-09-07 19:42:03">

BaseModel.all
=> []

I have also tried to change the type column of the inherited table to the basemodel

to = TypeOne.first
to.type = "BaseModel"
to.save

BaseModel.all
=> []

I have tried to change the classes for each to be a child of the BaseModel

TypeOne < BaseModel
TypeTwo < BaseModel
TypeThree < BaseModel

When I do this, I lose the connection to the existing data and each of the models appear empty.

How can I combine the existing tables?

Are there any foreign keys referencing the IDs of the records (e.g. type_one.id, type_two.id, etc.)? If not I'd recommend SELECTing from one table and INSERTing into BaseModel's table, that will change the ID so there won't be conflicts if you have your PRIMARY KEY set up correctly. Then use your inheritance structure as you have it — frankpinto
STI = Single table inheritance. To use it as its meant to be used you need all your data in one table — frankpinto
are there any clean ways of doing that for a production dataset? — casekey

frankpinto frankpinto · Accepted Answer · 2015-09-07T20:49:55

As you've tagged it with PostgreSQL I'll include how to do what I suggested in my comment as an answer:

INSERT INTO base_model SELECT * FROM type_one ORDER BY id ASC;
INSERT INTO base_model SELECT * FROM type_two ORDER BY id ASC;
INSERT INTO base_model SELECT * FROM type_three ORDER BY id ASC;

To safely do this on a production dataset, put the SQL in a migration in db/migrate (i.e. in a file like db/migrate/20150907185938_integrate_tables.rb) and test it on your local database first. This should get you pretty close:

class IntegrateTables < ActiveRecord::Migration
  def up
    execute "INSERT INTO base_model SELECT * FROM type_one ORDER BY id ASC;"
    execute "INSERT INTO base_model SELECT * FROM type_two ORDER BY id ASC;"
    execute "INSERT INTO base_model SELECT * FROM type_three ORDER BY id ASC;"
  end

  def down
    raise ActiveRecord::IrreversibleMigration, "It is unclear where original data stops and inserted data begins, can't migrate down"
  end
end

Please mark this answer as accepted if it worked for you :)

Add Single Table Inheritance (STI) to existing models

1 Answers