Rails 4 bulk updating array of models

Question

I have an array of ActiveRecord model and I want to renumber one column and bulk update them. Code looks like this:

rules = subject.email_rules.order(:number)
rules.each_with_index do |rule, index|
  rule.number = index + 1
end

EmailRule.update(rules.map(&:id), rules.map { |r| { number: r.number } })

But this creates N SQL statements and I would like 1, is there a way to do it?

Answer 1

Assuming you are using postgres you can use row_number and the somewhat strange looking UPDATE/FROM construct. This is the basic version:

UPDATE email_rules target 
SET number = src.idx
FROM (
  SELECT 
    email_rules.id,
    row_number() OVER () as idx
    FROM email_rules
) src
WHERE src.id = target.id

You might need to scope this on a subject and of course include the order by number which could look like this:

UPDATE email_rules target 
SET number = src.idx
FROM (
  SELECT 
    email_rules.id,
    row_number() OVER (partition by subject_id) as idx
    FROM email_rules
    ORDER BY number ASC
) src
WHERE src.id = target.id

(assuming subject_id is the foreign key that associates subjects/email_rules)