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

I'm not sure if this counts as a solution, but you don't literally need to do one update for every row. If you move 'foo' from position 4 to position 1, you just do

UPDATE table SET position = 1 WHERE itemname = 'foo'
UPDATE table SET position = position + 1 WHERE itemname != 'foo' AND position < 4

It's the same number of updates even if you're moving from position 1000 to 500, or from 500 to 1000 (although you'll need to flip it, naturally), you just need to mass shift all the affected rows plus or minus one

Answer 2

You can do it in a single Update statement like so:

Update Table
Set OrderValue = Case
                    When Table.ItemName = 'apples' Then 0
                    Else    (
                            Select Count(*)
                            From Table As T1
                            Where T1.ItemName <> 'apples'
                                And T1.OrderValue < Table.OrderValue
                            ) + 1
                    End + 1

You would obviously replace apples with the selected value. However, I would think that this type of sorting would best done in the client application rather than in the database.

Answer 3

If you were using SQL Server uou could do this using a linked-list representation and CTEs. I don't know whether mysql supports CTEs though...

SET NOCOUNT ON
GO

DROP TABLE [Item]
GO

CREATE TABLE [Item]
(
    [ItemId] int NOT NULL PRIMARY KEY,
    [Name] varchar(100) NOT NULL,
    [PreviousId] int NULL
)
GO

INSERT [Item] VALUES (6, 'apples', 3)
INSERT [Item] VALUES (3, 'orange', 36)
INSERT [Item] VALUES (9, 'mango', 100)
INSERT [Item] VALUES (100, 'salad', NULL)
INSERT [Item] VALUES (36, 'banana', 9)
GO

;WITH
[LinkedItem] AS
(
    SELECT
        [Item].*,
        1 AS [OrderValue]
    FROM [Item]
    WHERE [Item].[PreviousId] IS NULL
    UNION ALL
    SELECT
        [Item].*,
        [LinkedItem].[OrderValue] + 1
    FROM [Item]
        INNER JOIN [LinkedItem] ON [LinkedItem].[ItemId] = [Item].[PreviousId]
)
SELECT *
FROM [LinkedItem]
ORDER BY
    [LinkedItem].[OrderValue]

-- Drag orange up two spaces
DECLARE @MovingItemId int
DECLARE @NewPreviousId int
SET @MovingItemId = 3
SET @NewPreviousId = 100

DECLARE @OldPreviousId int
SELECT @OldPreviousId = [PreviousId] FROM [Item] WHERE [ItemId] = @MovingItemId
UPDATE [Item] SET [PreviousId] = @OldPreviousId WHERE [PreviousId] = @MovingItemId
UPDATE [Item] SET [PreviousId] = @MovingItemId WHERE [PreviousId] = @NewPreviousId
UPDATE [Item] SET [PreviousId] = @NewPreviousId WHERE [ItemId] = @MovingItemId

This produces the following before and after results:

100 salad  NULL    1
9   mango  100 2
36  banana  9   3
3   orange  36  4
6   apples  3   5

100 salad   NULL    1
3   orange  100 2
9   mango   3   3
36  banana  9   4
6   apples  36  5

Answer 4

I suppose you have a primary key on your table, an id column. These two statements should do.

update table set order_value=0 where itemname='apples';
update 
(select @num := 0 )vars
straight_join 
(select id, @num := @num+1 as ord_value
from table 
order by order_value
)big
inner join table t on t.id = big.id
set t.order_value = big.ord_value;

If you don't have an id, use itemname instead.

Answer 5

1. As has been suggested previously, and unless you have to show to all the users the current order that a given user is affecting, I would suggest that you treat this in the client first (there are many ways of solving this), and then, based on a user action (pressing the "I am done" button, for example) you update the rows in the database with the final order from the structure that you have chosen to store in the client.

2. You can make the code in the client as complex as you want to try to minimize the number of rows that need to be updated in the database: in some case, you may only need to insert one row (if the user inserts a new item at the end of the list); in many cases you may need to update two rows (if the user just swaps two consecutive items). The worst scenario as per the number of rows that need to be updated, is all the rows (you can device an algorithm that will only detect the rows that need to be updated and just update those). The choice is yours whether it is worth doing this or just issuing an update of all the rows.

3. The bottom line is that you do not need to update all the rows in the database, that situation is just one of the many possible scenarios. Some databases allow update in bulk (the name may vary from one database to another) and this will not be very expensive.