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.
Top comments (5)
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.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
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.
In your code snippet:
position
thenorder
, but I guess it's a typo.Fixed:
Add moving up and remove some useless
=
. (not tested tho)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.