0

Without any raw SQL, I'd like to be able to perform a particular denormalization using an ActiveRecord migration. I'm creating a new, unpopulated column in one table:

add_column :table_A, :some_data, :integer, :default => nil

Now, :table_A already has a reference to :table_B, via a has_many in :table_B's model class. :table_B also already has the :some_data column. What I want to do is to have :table_A's :some_data column get populated with the values in the same column in the corresponding :table_B rows (edit: once, in a single migration, not continuously), without using a loop (I want the resulting update operation to be a single statement). I'm attempting to navigate the ActiveRecord documentation to determine how to do this but I'm coming up empty.

https://apidock.com/rails/v4.0.2/ActiveRecord/Relation/update_all indicates to me how to do an update where the receiver is a single table, but I don't see how to pull data from a corresponding row in another table. https://apidock.com/rails/ActiveRecord/QueryMethods/joins indicates to me how to perform a join in AR, but I don't see how to then perform an update on the resulting data set, because I don't know how to distinguish between the two tables in the parameter to update_all. That is, if I write TableA.joins(:table_b).update_all(...), what do I put in place of the ... there?

EDIT: There's a really weird set of constraints on this task that I'm not happy about having to work within. If I could change the environment such that they weren't in place I would do so, but I can't. (Honestly I'd get rid of Rails entirely in that case.)

We use AR migrations in dev and QA environments, but run SQL manually in staging and production. (No, I don't understand this decision either, but I have no control over it.) What this means is that I would like anything I write in an AR migration to deterministically generate the same SQL regardless of the data present in the DB at the time the SQL is generated (via the lol_dba gem), which in turn means no loops because the resulting SQL is data-dependent.

It appears to me from these answers and from other research that this means I need to write SQL separately to perform the data propagation I need, rather than doing it as part of a migration. If we weren't in this weird setup where production used generated SQL to do migrations, this task would be much simpler and basically any of the posted answers would suffice.

Rick Woods
  • 13
  • 3
  • Why do you have the requirements of not using raw SQL or loops? Rails migrations are typically used for DDL only (although this is an opinion). I don't see a way to accomplish what you are looking for given the requirements. If you leverage Rails associations (with a loop, however), there is a simple solution without using raw SQL. – Mark Merritt Feb 04 '20 at 00:16
  • If it involves raw SQL I'm going to just write SQL and skip Rails entirely, which is what my fallback is right now. I don't want a loop because I don't want the migration to result in a separate update operation being performed for every row in the table. – Rick Woods Feb 04 '20 at 00:31
  • Sorry what is your reason to avoid to have a loop ? Is it a performance concern ? Or you Don't want to change uselessly the `updated_at` column ? Just for a better understanding of your needs. – morissetcl Feb 04 '20 at 08:55

1 Answers1

0

You can do this in rails console or a rake task, not migration:

  TableA.all.map{ |a| a.update(some_data: a.table_b.some_data) }

e.g. if TableA is Child and TableB is Father and some_stuff is nationality:

  Child.all.map{ |child| child.update(nationality: child.father.nationality) }

For further instance-creations of TableA, you will definitely need to handle that in controller (the proper way) or in model.

ARK
  • 772
  • 7
  • 21
  • The better solution IMHO is the one I gave. This might help you in the way you want but not a good thing I guess: https://stackoverflow.com/q/15284495/10313894 – ARK Feb 04 '20 at 16:10
  • update_all, each and map are all loops at the end of the day. – ARK Feb 04 '20 at 16:11