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

[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.