Rails update_all from associated_object

Question

I have a Glass object and a Prescription object, but i forgot to add timestamps to the Glass Object, so i created a migration to do that. However, not surprisingly all the objects have todays date and time.

glass belongs_to :prescription prescription has_one :glass

However, I can get the correct timestamp from the Prescription object. I just don't know how to do that. So I want to do something like

Glass.update_all(:created_at => self.prescription.created_at)

any ideas ?

Answer 1

Easiest thing to do is simply multiple SQL queries, it's a one off migration so no biggie I think. ActiveRecord update_all is meant to update the matching records with the same value so that won't work.

Glass.all.find_each do |glass|
   glass.update!(created_at: glass.prescription.created_at)
end

If you want one query (update based on a join - called "update from" in sql terms) it seems not straightforward in ActiveRecord (should work on MySQL but not on Postgres) https://github.com/rails/rails/issues/13496 it will be easier to write raw SQL - this can help you get started https://www.dofactory.com/sql/update-join

Answer 2

You can use touch method

Prescription.find_each do |prescription|
  prescription.glass.touch(:created_at, time: prescription.created_at)
end

Answer 3

Believe me when I say that I'm on team "idiomatic Rails" and it's true that iterating through each record and updating it is probably more idiomatic, but UPDATE FROM.. is so incredibly more performant and efficient (resources-wise) that unless the migration is iterating through < 1000 records, I prefer to do the in-SQL UPDATE FROM.

The particular syntax for doing an update from a join will vary depending on which SQL implementation you're running (Postgres, MySQL, etc.), but in general just execute it from a Rails DB connection.

InboundMessage.connection.execute <<-SQL
  UPDATE
    inbound_messages
  INNER JOIN notifications
    ON inbound_messages.message_detail_type = "Notification"
    AND inbound_messages.message_detail_id = notifications.id
  SET 
    inbound_messages.message_detail_type = notifications.notifiable_type,
    inbound_messages.message_detail_id = notifications.notifiable_id
  WHERE
    notifications.type = "foo_bar"
SQL