I have a list with objects stored in a database. These objects are shown in a list and the user can drag and drop them to order them in a specific way. I want that specific order to be stored.
How can I do this in an efficient manner? I’ve thought of the following options:
-
Maintain an array with the desired order. Array can be stored locally (not an option in my current project) or online (extra table :(). I don’t really like this solution.
-
Add a “priority” field to each object, and sort the objects by it. By dragging and dropping the priority of one object is changed to a value < the object above and > than the object beneath. There should be a big margin between those priority values though, or I should use halve numbers/float (infinite to platform’s float definition). Else one might end up trying to decide what number is bigger than 2 and smaller than 3, for example. Update the updated object only.
-
Same as 2. but update ALL objects after a drag and drop action, and store them all. This feels a bit inefficient and unnecessary.
Am I missing the obvious? Is there a better way?
2
You’re probably overthinking the efficiency aspect. Any list small enough for a human to reasonably reorder via drag and drop is trivial for a computer to manipulate, even with very “inefficient” algorithms. Also keep in mind that humans are very slow, and updating the order is a relatively infrequent operation compared to querying for it.
However, I believe the algorithm with the best tradeoffs between query time and update time would be what I call the BASIC algorithm. BASIC programs used to have integer line numbers to make interactive programming easier. Because you might need to insert a line between two lines, you would start out numbering them by 10s. If you needed to insert a line between, you’d make it a multiple of 5, and so on. If you ran out of space in between, there was a renum command that put everything back to multiples of 10.
Using this method to sort objects would mean most of the time, you just need to update the one row, with the occasional renumbering of the entire list when you run out of space in between. Since the numbers wouldn’t be user-visible, you can put quite a bit of space in between, so the renumberings should be relatively rare. That gives you a worst case update of O(n), but an amortized update close to O(1).
1