Updating a database display order with drag and drop in SQL

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:

  1. Current position
  2. Desired position
  3. 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.