What are best practices for persisting positions when using drag & drop?

We're in the process of implementing drag & drop in our platform to reorder elements and while the UX is straightforward to manage I've seen a lot of options for persisting the order in DB.

I'd love to get some wisdom from the community and see what the best practices are. I've looked around on StackOverflow and found this article that has an interesting option to limit queries.

So, in your experience, what's the best approach for it?

---

Edit: for those stumbling on the post look at Guillaume's comment for neat SQL statements.

Comments

[Casey Brooks]

Generally speaking, for a drag an drop list, the number of elements you're working with is going to be small enough that reordering the list with a order column should be fine. If you have so many elements that you're needing to paginate the list, then drag and drop doesn't really make sense (how do you move an item between pages?). The code is pretty straightforward too, select *, then when go to save, just iterate through the list and set its order to the iteration index and save.

[Sten]

Yeah, I agree that there's not a good use case for paginated drag & drop. In our case we went ahead and used prepared statements akin to

update table 
  set position = position - 1
  where position >= <current_position> and position <= <new_position>;

update table
  set position = <new_position>
  where id = <object_id>

It's a bit more complex as we have our ordering is scoped to another object (we have goals that can be ordered within sections and moved from one section to another). That seems to be working pretty well for us.

[Guillaume Martigny]

In your code snippet:

• You use position then order, but I guess it's a typo.
• You decrements position of elements being pushed down (when moving up), but shouldn't you increments position of element being pushed up (when moving down) ?

[Guillaume Martigny]

Fixed:

update table -- When moving item down
  set position = position - 1
  where current_position < position and position <= new_position;

update table -- When moving item up
  set position = position + 1
  where new_position < position and position <= current_position

update table
  set position = <new_position>
  where id = <object_id>

Add moving up and remove some useless =. (not tested tho)

[Sten]

Thanks, I fixed the typo and yes there's a second statement to push things up when moving down. This was totally inspired by this StackExchange post.

There are also some other statements to push compact list 1 and push things down in list 2 when moving things from list 1 to list 2.