Rails: How to increment an integer field of selected instances of a model?

Question

Buyer model has two fields:

• name (string)
• position (integer)

I would like to increment the position of all buyers whose position >= N.

What is the easiest method to do this ?

Is that possible to achieve this using only one query ?

Answer 1

You could use:

Buyer.update_all("position = position + 1", ["position >= ?", n])

This would generate the query, if n = 25:

UPDATE "buyers" SET position = position + 1 WHERE (position >= 25)

Edit:

Being that you have UNIQUE database constraints, you have a couple of options. For both options, I recommend running them in a transaction. First, you can update each field individually in reverse order, but this will cause you to have N+1 queries. For a small dataset, this will not be a problem, but for larger dataset, this could impact performance.

Buyer.transaction do
   Buyer.select("id, position").where(["position >= ?", n]).order("position DESC").each do |buyer|
      buyer.position += 1
      buyer.save
   end
end

The other option, to avoid N+1 queries, is to change the position increments to 100 (or 10). This will allow you to update the positions in two queries, rather than N+1. So instead of having positions 1, 2, 3, etc. you would have 100, 200, 300, etc. Then to do an update, you would increment all values by 101, and then follow update with an update to subtract the 1.

Buyer.transaction do
   Buyer.where(["position >= ?", n]).scoping do
      Buyer.update_all("position = position + 101")
      Buyer.update_all("position = position - 1")
   end
end

Answer 2

If this is ad-hoc, you could drop the constraint/index, run the update, and then re-add it using regular old SQL.