Update a list of things without hitting every entry

Posted by bobobobo on Stack Overflow See other posts from Stack Overflow or by bobobobo
Published on 2010-05-13T04:34:13Z Indexed on 2010/05/13 4:44 UTC
Read the original article Hit count: 258

Filed under:
|
|

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?

© Stack Overflow or respective owner

Related posts about sql

Related posts about mysql