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/CADGZSSdGfujknDfL3%3DwsFBaSenp-J1gdMh_2FzYZy5pVXx-k%3DA%40mail.gmail.com.
