Thanks Jeremy. That worked.
Adding both the solutions here if anyone else needs it.
*Solution 1 (without 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 (with 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
On Saturday, April 24, 2021 at 10:32:09 PM UTC+5:30 Jeremy Evans wrote:
> On Sat, Apr 24, 2021 at 4:08 AM abhijit <[email protected]> wrote:
>
>> Thanks Jeremy.
>>
>> The non-window functions version worked seamlessly -
>> 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
>>
>> I really wanted to try the version with window functions, since I'm using
>> Postgres, but I got this error -
>> undefined local variable or method `row_number'
>>
>> I had tried this (with some changes to the code you gave). I'm sure I'm
>> not writing the function correctly.
>> from(:new_table).insert(
>> [
>> :id,
>> :orderid,
>> :position
>> ],
>> from(:orderdetails).select([
>> :id,
>> :orderid,
>> row_number.function.order(:id).partition(:orderid)
>> ]) {}
>> )
>>
>
> This wouldn't work, because `row_number` needs to be called inside the
> block passed to select..
>
>
>> I had also tried -
>> Sequel.function(:row_number) .order(:id).partition(:orderid)
>>
>
> This should work, except that I missed one thing. You need to call over
> with the :order and :partition options, instead of separate order and
> partition methods:
>
> Sequel.function(:row_number).over(order: :id, partition: :orderid)
>
> Thanks,
> Jeremy
>
--
You received this message because you are subscribed to the Google Groups
"sequel-talk" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/sequel-talk/3e6f1584-b2aa-4f45-a535-cce67a75f1bfn%40googlegroups.com.
