Sort based on another column / batch re-rank

[chrisbarber]

Not sure if this is a very common use-case, but I thought I'd mention it in case you find it worth thinking about.

I ended up needing to re-rank all elements based on another column in the table. E.g.

id	row_order	other_column
9	0	9
10	4194304	1
11	6291456	2

And you want to re-rank based on other_column ASC:

id	row_order	other_column
9	6291456	9
10	0	1
11	4194304	2

One way to do this in ranked-model would be like

Duck.order(:other_column => :asc).all.each_with_index do |duck, index|
  duck.update_attribute :row_order_position, index
end

For my case I ended up solving this using raw SQL which isn't the most desirable, but it is definitely efficient. It uses UPDATE FROM which I don't think is very portable, and ROW_NUMBER(), to basically just flip around the row_order column according to the desired sort order:

UPDATE ducks SET row_order = src.t1_roworder FROM
  (SELECT * FROM
    (SELECT *, row_order t1_roworder, ROW_NUMBER() OVER (ORDER BY row_order ASC) t1_rownum FROM ducks) t1 JOIN
    (SELECT *, ducks.id t2_id, ROW_NUMBER() OVER (ORDER BY other_column ASC) t2_rownum FROM ducks) t2
  ON t1_rownum = t2_rownum
  ORDER BY t2_id
  ) AS src
WHERE ducks.id = src.t2_id

[jaredmdobson]

This was just what I needed thanks @chrisbarber! :+1:

[brendon]

Closing due to inactivity.