Rails: how to increase the integer field of selected model instances?

Question

Buyer model has two fields:

• name (string)
• position (integer)

I would like to increase the position all buyers whose position >= N

What is the easiest way to do this?

Is it possible to achieve this using only one query?

Answer 1

You can use:

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

This will result in a query being generated if n = 25:

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

Edit:

Since you have UNIQUE database limitations, you have several options. For both options, I recommend using them in a transaction. Firstly, you can update each field individually in the reverse order, but this will lead to the appearance of N + 1 queries. For a small dataset this will not be a problem, but for a larger dataset this can affect performance.

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

Another option to avoid N + 1 queries is to change the position increment to 100 (or 10). This will allow you to update the positions in two queries, and not in N + 1. Thus, instead of having positions 1, 2, 3, etc., you will have 100, 200, 300, etc. Then, to make an update, you must increment all the values ​​by 101, and then follow the update with the update to subtract 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 it is ad-hoc, you can remove the constraint / index, start the update, and then re-add it using plain old SQL.

Answer 3

 class Buyer < ActiveRecord::Base scope :positioned_at_or_above, lambda {|pos| where("position >= ?", pos) } def self.increment(amount, position_threshold) Buyer.positioned_at_or_above(position_threshold).each{|buyer| buyer.update_attributes(:position => buyer.position + amount)} end end 

-

 increment ∴ rails c Loading development environment (Rails 3.0.3) >> Buyer.count => 0 >> (1..10).each {|idx| Buyer.create(:name => "Buyer ##{idx}", :position => idx)} => 1..10 >> pp Buyer.all [#<Buyer id: 11, name: "Buyer #1", position: 1>, #<Buyer id: 12, name: "Buyer #2", position: 2>, #<Buyer id: 13, name: "Buyer #3", position: 3>, #<Buyer id: 14, name: "Buyer #4", position: 4>, #<Buyer id: 15, name: "Buyer #5", position: 5>, #<Buyer id: 16, name: "Buyer #6", position: 6>, #<Buyer id: 17, name: "Buyer #7", position: 7>, #<Buyer id: 18, name: "Buyer #8", position: 8>, #<Buyer id: 19, name: "Buyer #9", position: 9>, #<Buyer id: 20, name: "Buyer #10", position: 10>] => nil >> pp Buyer.positioned_at_or_above(4) [#<Buyer id: 14, name: "Buyer #4", position: 4>, #<Buyer id: 15, name: "Buyer #5", position: 5>, #<Buyer id: 16, name: "Buyer #6", position: 6>, #<Buyer id: 17, name: "Buyer #7", position: 7>, #<Buyer id: 18, name: "Buyer #8", position: 8>, #<Buyer id: 19, name: "Buyer #9", position: 9>, #<Buyer id: 20, name: "Buyer #10", position: 10>] => nil >> pp Buyer.positioned_at_or_above(4).all [#<Buyer id: 14, name: "Buyer #4", position: 4>, #<Buyer id: 15, name: "Buyer #5", position: 5>, #<Buyer id: 16, name: "Buyer #6", position: 6>, #<Buyer id: 17, name: "Buyer #7", position: 7>, #<Buyer id: 18, name: "Buyer #8", position: 8>, #<Buyer id: 19, name: "Buyer #9", position: 9>, #<Buyer id: 20, name: "Buyer #10", position: 10>] => nil >> Buyer.increment(1000, 4) => [#<Buyer id: 14, name: "Buyer #4", position: 1004>, #<Buyer id: 15, name: "Buyer #5", position: 1005>, #<Buyer id: 16, name: "Buyer #6", position: 1006>, #<Buyer id: 17, name: "Buyer #7", position: 1007>, #<Buyer id: 18, name: "Buyer #8", position: 1008>, #<Buyer id: 19, name: "Buyer #9", position: 1009>, #<Buyer id: 20, name: "Buyer #10", position: 1010>] >> pp Buyer.all [#<Buyer id: 11, name: "Buyer #1", position: 1>, #<Buyer id: 12, name: "Buyer #2", position: 2>, #<Buyer id: 13, name: "Buyer #3", position: 3>, #<Buyer id: 14, name: "Buyer #4", position: 1004>, #<Buyer id: 15, name: "Buyer #5", position: 1005>, #<Buyer id: 16, name: "Buyer #6", position: 1006>, #<Buyer id: 17, name: "Buyer #7", position: 1007>, #<Buyer id: 18, name: "Buyer #8", position: 1008>, #<Buyer id: 19, name: "Buyer #9", position: 1009>, #<Buyer id: 20, name: "Buyer #10", position: 1010>] => nil >>