CODEWITHSUNDEEP
mysqlruby-on-railsrubyactiverecord
rj487
rj487

Reputation: 4634

Rails update multiple record with hash

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.

Upvotes: 0

Views: 809

Answers (2)

fongfan999
fongfan999

Reputation: 2624

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.

Upvotes: 1

Moamen Naanou
Moamen Naanou

Reputation: 1713

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

Upvotes: 1

Related Questions