Ruby Sequel migration – increment column in a group

The requirement was to add a column called position to the order_details table.

The order_details table is actually a table with a foreign key of order_id. The requirement is to increment the position starting from 1 in the position column grouped on order_id.

So, before the migration if the records are –

id  order_id
1   1
2   1
3   1
4   2
5   2
6   2

After the migration, the records should be –

id  order_id position
1   1       1
2   1       2
3   1       3
4   2       1
5   2       2
6   2       3

There are two solutions possible. One using window functions and one without.

Solution 1 (without using window function)

Sequel.migration do
  up do
    alter_table :order_details do
      add_column :position, Integer, default: 1, allow_null: false
    end
    
    from(:order_details).order(:orderid, :id).select_hash_groups(:orderid, :id).each_value do |ids|
      i = 0
      ids.each do |id|
        from(:order_details).where(id: id).update(position: i+=1)
      end
    end
  end

  down do
    alter_table :order_details do
      drop_column :position
    end
  end
end

Solution 2 (using window function)

Sequel.migration do
  up do
    create_table :new_table do
      primary_key  :id
      Integer :position, :default => 1, :allow_null => false
      foreign_key  :orderid, :orders, :key=>:id
    end

    from(:new_table).insert(
      [ :id, :orderid, :position ],
      from(:order_details).select(
        :id,
        :orderid,
        Sequel.function(:row_number).over(order: :id, partition: :orderid)
      )
    )

    drop_table :order_details
    rename_table :new_table, :order_details
  end

  down do
    alter_table :order_details do
      drop_column  :position
    end
  end
end

Reference: sequel-talk