Rails update multiple record with hash

Question

I need to update my data iteratively.

But the following way I achieved is the way too time-consuming.

Can I update multiple records with an id-value hash?

SUBST = ''.freeze
re = /<p>|<\/p>/m
(1..1000).each do |id|
  choice = QuestionChoice.find id
  choice.selections.gsub!(re, SUBST)
  choice.save! if choice.changed?
end

Update:

Since I found out my code could be improved by using where

Like the following

QuestionChoice.where(id: (1..1000)).each do |choice|
  choice.selections.gsub!(re, SUBST)
  choice.save! if choice.changed?
end

But now I still need to call save! for every record which will cost much time.

Hi @Coda Chang, is there any callbacks when a choice is saved? — fongfan999, May 29 '18 at 05:34
You could try batches - http://api.rubyonrails.org/classes/ActiveRecord/Batches.html — Disha, May 29 '18 at 05:50
@fongfan999 is on the right track: do it all inside the database. — mu is too short, May 29 '18 at 07:25
@fongfan999, yeah, it will return `true` if save successfully. — rj487, May 29 '18 at 09:49

score 1 · Answer 1 · answered May 29 '18 at 05:43

1

You are hitting the db 1000 times sequentially to get each record separately, try to use single query to get all records you need to update:

SUBST = ''.freeze
re = /<p>|<\/p>/m
QuestionChoice.where('id <= 1000').map do |q| 
  q.selections.gsub!(re, SUBST) 
  q.save! if q.changed?
end

answered May 29 '18 at 05:43

Moamen Naanou

1,683
1
21
45

Oh, but in this way, the q still need to be `save` for 1000 times. – rj487 May 29 '18 at 09:44
I've updated my question. the way is a good way to save `find` query, but I am looking for saving the `update` query. – rj487 May 29 '18 at 09:47
@CodaChang if you are using `MySQL 8.0+` you can use `update_all` and make use of DB functionality `REGEXP_REPLACE` as per this answer https://stackoverflow.com/a/49925597/5746504 – Moamen Naanou May 29 '18 at 11:59
Unfortunately, I am using MySQL 5.6, but it's also a good reference. Thx. – rj487 May 30 '18 at 04:02

fongfan999 · Answer 2 · 2019-01-24T13:22:08.620

I used to face this problem and I solved it. Try to the following:

MySQL 8.0+:

QuestionChoice.where(id: 1..1000).update_all("selections = REGEXP_REPLACE(selections, '<p>|<\/p>', '')")

Others:

QuestionChoice.where(id: 1..1000).update_all("selections = REPLACE(selections, '</p>', '')")

or

QuestionChoice.where(id: 1..1000).update_all %{
  selections =
    CASE 
    WHEN selections RLIKE '<p>|<\/p>' 
    THEN REPLACE(selections,'<p>|<\/p>', '')
    END 
    WHERE selections RLIKE '<p>|<\/p>'
}

IMPORTANT: Try to put a few backlashes (\) to your regex pattern in the clause if needed.

Rails update multiple record with hash

Update:

2 Answers2