In many of our applications we support dragging and dropping datasets to change the order. We save this order in a database column that is an integer and is in sequential order.
- Current position
- Desired position
- User’s id
Example data set for table todos
|2000||1||Take out garbage|
|2000||5||Change light bulb|
Step #1 – Determine the position
Step #2 – Update the dragged item
Step #3 – Move the item down
Step #4 – Move the item up
Step #5 – Update the dragged item to the desired position
With this approach the server will do a total of three queries for every change to the display order no matter how many items are in the list. Examples typically tell you to iterate over every item to do an update query to set the new order. That approach results in total queries = total amount of items. This new approach is a significant improvement and has reduced a lot of our large data sets from 40+ queries down to three.