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.
There are many ways in javascript to handle drag and drop. I will show an example using jquery sortable. If you’d like to use something else there are three pieces of key information that need to be sent to the server:
- Current position
- Desired position
- User’s id
HTML list
Javascript sortable
Example data set for table todos
| user_id | display_order | todo |
|---|---|---|
| 2000 | 1 | Take out garbage |
| 2000 | 2 | Clean house |
| 2000 | 3 | Do dishes |
| 2000 | 4 | Cut grass |
| 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.