News for hackers. Stuff that matters.
Re:User-defined Order in SQL by flomo on Wednesday March 21, 2018 @02:15AM (#16635916)
I implemented something like this in a naive way. I took the modified date (previous default sort order) and stored the unix timestamp * 100 in a 64 bit indexed SortVal column. Drag-n-drop sets the SortVal +/-1 relative to the item it is being dropped against. There's only ~30 items per page so I figure it won't break except in the pathological case.

Curious if there is some obvious flaw with this plan or if I really need the power of true fractions.

Re:User-defined Order in SQL by gluxon on Wednesday March 21, 2018 @02:27AM (#16635961)
How do you handle situations where two items (lets call them A and B) are both moved before a third item C?

Then wouldn't A and B both have a "SortVal" that's 1 less than C's SortVal? That would make your SortVals non-unique and order arbitrary.

Re:User-defined Order in SQL by jve on Wednesday March 21, 2018 @04:30AM (#16636392)
Well, only if your UI allows dragging two items at once. If you reorder multiple items, well, I suppose you can resolve it by passing some relative position. If you want to squeeze something in-between reordered items, then -1 probably is not a good choice, but more like calculating some middle value between previous and next item.
Re:User-defined Order in SQL by grzm on Wednesday March 21, 2018 @04:35AM (#16636409)
What would the timestamp represent? Are you providing a timestamp which encodes the position somehow, or using CURRENT_TIMESTAMP? Updating the timestamp for all of the rows in the (sub) list? How does this permit you to reorder the items in the list?
Re:User-defined Order in SQL by jve on Wednesday March 21, 2018 @08:39AM (#16637496)
I haven't implemented this, just brainstorming.

Timestamp would only represent position and enables to put something in between, rather than an integer which adds +1. If put A (timestamp = 100) between B (timestamp = 200) and C (timestamp = 300), just set that order value to middle (150). Naive

It actually looks the same as "What about leaving room?" and, yeah, introduces complexity when you organize items for too long.