Rails : Moving a column from a table to another table

Question

I'm a beginner and I can't move a table column(doctors.specialty) to another table column(specialties.name).

This is the doctors table:

tp Doctor.all

ID	FIRST_NAME	LAST_NAME	SPECIALTY	ZIP_CODE	CREATED_AT	UPDATED_AT	CITY_ID
1	Cathy	Wyman	Gynecologist	78328	2022-07-28 13:48:57	2022-07-28 14:33:51	17
2	Dave	Howell	General practitioner	97544	2022-07-28 13:48:57	2022-07-28 14:33:51	13
3	Zachariah	Stamm	Anesthesiology	08435-1702	2022-07-28 13:48:57	2022-07-28 14:33:51	4
4	Forrest	Koelpin	General practitioner	58486	2022-07-28 13:48:57	2022-07-28 14:33:51	16
5	Cameron	Zieme	Anesthesiology	01200	2022-07-28 13:48:57	2022-07-28 14:33:51	13
6	Chia	Borer	Gynecologist	81913-8875	2022-07-28 13:48:57	2022-07-28 14:25:11	18
7	Sonny	Torphy	Gynecologist	08593-7841	2022-07-28 13:48:57	2022-07-28 14:33:51	4

I generated a new model:
rails g model Specialty name:string doctor_id:integer

I generated a new migration
rails g migration MoveColumnDataToSpecialty

I tried this code but it didn't work and i'm not sure that I should use it to move the column

class MoveColumnDataToSpecialty < ActiveRecord::Migration[5.2]
  def self.up
    add_column :users, :some_property, :string
    execute "UPDATE users u SET some_property = p.some_property FROM profiles p WHERE u.id = p.user_id;"
    remove_column :profiles, :some_property
  end

  def self.down
    add_column :profiles, :some_property, :string
    execute "UPDATE profiles p SET some_property = u.some_property FROM users u WHERE p.user_id = u.id;"
    remove_column :users, :some_property
  end
end

Thank you in advance

rails g model Specialty name:string doctor_id:integer -- don't sure it's good idea. Column doctors.speciality_id looks better than specialities.doctor_id — mechnicov

mechnicov mechnicov · Accepted Answer · 2022-07-28T18:19:04

Firstly you need to create specialties table, then add column with FK to doctors table. After that insert into specialties existed names and make associations between tables. Finally you can remove unused column

Rollback migration -- reverse order

def self.up
  create_table :specialties do |t|
    t.string :name
  end

  change_table :doctors do |t|
    t.belongs_to :specialty, foreign_key: true
  end

  specialties = Doctor.pluck(:specialty).uniq

  specialties_ids_names =
    Specialty.insert_all!(
      specialties.map { |specialty| { name: specialty } },
      returning: %w[id name]
    )

  specialties_ids_names.rows.each do |specialty_id, name|
    # Here raw SQL column to prevent disambiguates with association
    Doctor.where('doctors.specialty = ?', name).update_all(specialty_id: specialty_id)
  end

  remove_column :doctors, :specialty
end

def self.down
  add_column :doctors, :specialty, :string

  Specialty.pluck(:id, :name).each do |specialty_id, name|
    # Here raw SQL column to prevent disambiguates with association
    Doctor.where(specialty_id: specialty_id).update_all("specialty = #{name}")
  end

  remove_column :doctors, :specialty_id

  drop_table :specialties
end

Note: insert_all was introduced in Rails 6. Instead of this method, you can create records one by one or use raw SQL INSERT INTO specialties VALUES using ActiveRecord::Base.connection.insert_sql

Rails : Moving a column from a table to another table

1 Answers