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)
]) {}
)
I had also tried -
Sequel.function(:row_number) .order(:id).partition(:orderid)
But that didn't help either.
On Saturday, April 24, 2021 at 12:49:32 AM UTC+5:30 Jeremy Evans wrote:
> On Fri, Apr 23, 2021 at 1:59 AM abhijit <[email protected]> wrote:
>
>> I have to add a migration for adding a column called *position* in the
>> *ordedetails* table
>>
>> Since this is a sub table with a foreign key of *orderid*, I would like
>> to increment the position starting from 1 in the *position* column
>> grouped on *orderid*
>>
>> So, before the migration if the records are -
>> id orderid
>> 1 1
>> 2 1
>> 3 1
>> 4 2
>> 5 2
>> 6 2
>>
>> After the migration, the records should be -
>> id orderid position
>> 1 1 1
>> 2 1 2
>> 3 1 3
>> 4 2 1
>> 5 2 2
>> 6 2 3
>>
>> Any help is appreciated.
>>
>
> Without window function support, you could do:
>
> from(:table).order(:orderid, :id).select_hash_groups(:order_id,
> :id).each_value do |ids|
> i = 0
> ids.each{|id| from(:table).where(id: id).update(position: i+=1)
> end
>
> You could probably use a window function approach to do it in a single
> query, if your database supports window functions. You would create a new
> table, select into that table, then drop the current table and rename the
> new table. The insert would look something like this:
>
> from(:new_table).insert([:id, :orderid, :position],
> from(:table).select do [
> :id,
> :orderid,
> row_number.function.order(:id).partition(:orderid)
> ] end
> )
>
> I didn't test either of the above, so there may be errors, but hopefully
> it at least helps provide direction.
>
> 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/8d193dd4-4df4-4e61-9f32-00090e49524cn%40googlegroups.com.
