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

Question

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

Answer 1

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.