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

Buyer model has two fields:

• name (string)
• position (integer)
开发者_如何学Python

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 ?

---

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

---

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

---

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
>>