Update a list of things without hitting every entry

Question

I have a list in a database that the user should be able to order.

itemname|  order value (int)
--------+---------------------         
salad   |  1
mango   |  2
orange  |  3
apples  |  4

On load from the database, I simply order by order_value.

By drag 'n drop, he should be able to move apples so that it appears at the top of the list..

itemname|  order value (int)
--------+---------------------         
apples  |  4
salad   |  1
mango   |  2
orange  |  3

Ok. So now internally I have to update EVERY LIST ITEM! If the list has 20 or 100 items, that's a lot of updates for a simple drag operation.

itemname|  order value (int)
--------+---------------------         
apples  |  1
salad   |  2
mango   |  3
orange  |  4

I'd rather do it with only one update. One way I thought of is if "internal Order" is a double value.

itemname|  order value (double)
--------+---------------------         
salad   |  1.0
mango   |  2.0
orange  |  3.0
apples  |  4.0

SO after the drag n' drop operation, I assign apples has a value that is less than the item it is to appear in front of:

itemname|  order value (double)
--------+---------------------         
apples  |  0.5
salad   |  1.0
mango   |  2.0
orange  |  3.0

.. and if an item is dragged into the middle somewhere, its order_value is bigger than the one it appears after .. here I moved orange to be between salad and mango:

itemname|  order value (double)
--------+---------------------         
apples  |  0.5
salad   |  1.0
orange  |  1.5
mango   |  2.0

Any thoughts on better ways to do this?

Answer 1

Suppose @old is the value 4 for the old position of apples, and @new is the new position 1.

set @old = 4;
set @new = 1;

UPDATE Items
SET `order value` = 
  CASE `order value` WHEN @old THEN @new
  ELSE `order value` + SIGN(@old-@new) END
WHERE `order value` BETWEEN LEAST(@old, @new) AND GREATEST(@old, @new);

I used MySQL 5.1.52 to test this on your example data and it works. The identical SQL also works if you need to move an early entry to be later, or move one in the middle, etc. Just set the values of @old and @new.

Answer 2

I ended up using an adjacencies table. I didn't know about it at the time.