How do I reorder a table?
Say I have a list of to-do items and want my user to be able to reorder them as they please. Could I change the order of items without adding 1 to the sort column of every item that comes afterwards every time they do an operation?
3 Replies
Same thing for inserting items in-between others as well.
Been awhile since I was in a computer science class....
You could maintain a separate table that just has a json or array column with the id's in order. So you have a row per list and need one extra update per change in the list. (just rewrite the column with all the ids)
Maybe a linked list, if you don't mind post sorting your data after and loading all the rows in. The link col points to its parent with the first column have 0 for the parent. Then you sort by getting 0, then the id of that column in the link column, etc. To insert or change you need to modify the current row link and the row "below" it, changing their parent columns.
There is probably some way to use weight column with gaps inserted at first which would reduce when you have to reorder the columns below each gap, but at some point would need to re-weight them if the list grows enough. Similar thing could be done with strings and alphabetical order (A,B then A,AB,B). Seems pretty messy though, but would allow the database to return the items in order, without a post sort.
Looks like the same kind of suggestions here: https://stackoverflow.com/questions/3379414/reordering-an-ordered-list
Stack Overflow
Reordering an ordered list
I have a following SQL table with data
ProductList
id order productname
79 1 name1
42 2 name2
67 3 somename
88 4 othername
99 5 XYZ
66 6 ABC
Display order is...