CODEWITHSUNDEEP
sqlruby-on-railsactiverecordincrementupdate-all
John.Mazzucco
John.Mazzucco

Reputation: 289

Activerecord or SQL - update each record with an incremented value with a single statement

I have a cards table with an attribute called 'position', which has a default value of 0.

I need to select a group of cards, then assign the position of each card with an incremented value.

So, lets say that I select a group of cards using

cards = Card.where(id: [3,4,7,8]).

Can a single Activerecord or SQL statement assign an incremented value, that would give a result like this?

cards[0].position 
=> 1
cards[1].position
=> 2  
...

Upvotes: 1

Views: 624

Answers (1)

fbelanger
fbelanger

Reputation: 3578

ActiveRecord, no.

SQL, of course, always.

The issue here with ActiveRecord is that you're trying to do two things simultaneously.

  1. Update records using a single query.
  2. Prime the first record with n, where n += 1 for each record.

The problem is that when we're querying, we leave the Ruby execution and entering SQL.

i = 1
Card.update_all(position: i+=1)

The updated value will be 2 for all the records.

It's easy to update all the records with the same value, however we aren't updating all records with the same value here.

So you can't use ActiveRecord like this.

If you want to use Ruby to keep the counter, you must do something like:

Card.find_each(where: [3,4]).with_index(1) do |card, index|
  card.update(position: index)
end

Otherwise, you will need to do some SQL work.

Upvotes: 0

Related Questions