What are best practices for persisting positions when using drag & drop?
#discuss#webdev
Sten

Sten @stenpittet

About: Co-founder @Tability, previously @Atlassian

Location:
Sydney
Joined:
Mar 26, 2018

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

Publish Date: Oct 3 '18
15 5

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 5 total

  • Casey Brooks
    Casey BrooksOct 3, 2018

    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
      StenOct 3, 2018

      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
        Guillaume MartignyOct 3, 2018

        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
          Guillaume MartignyOct 3, 2018

          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
            StenOct 3, 2018

            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.

Add comment