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?

If this is human-generated data, then at maximum it's probably dozens of items. Most humans would lose interest manually sorting more than a few dozen (and the UI for doing it will break past about 20 unless you have a real big screen). So just do it. It would be a different story if you were resorting 1000s or millions of items. — Seth, May 13 '10 at 05:17

score 3 · Answer 1 · answered Feb 13 '11 at 22:50

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.

score 1 · Accepted Answer · edited May 23 '17 at 11:51

1

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

edited May 23 '17 at 11:51

Community

1
1

answered Feb 14 '11 at 03:13

bobobobo

64,917
62
258
363

score 0 · Answer 3 · answered May 13 '10 at 04:46

0

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

answered May 13 '10 at 04:46

Michael Mrozek

169,610
28
168
175

Yeah, I'm looking for small __worst case__ number of update hits – bobobobo May 13 '10 at 04:53
@bob I think maybe you misunderstood; this is two updates no matter what: one to change the actual row, and one to fixup all the other affected rows – Michael Mrozek May 14 '10 at 01:06

score 0 · Answer 4 · answered May 13 '10 at 04:58

0

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.

answered May 13 '10 at 04:58

Thomas

63,911
12
95
141

So what about an insertion into the middle of the list? I suppose this could be placed in a stored procedure and the index in the list where `apples` now "goes" could be an argument. I would do this in the client, but I must reflect it in the database immediately in case the client x's out of the browser, so that the view is loaded in the order the client last left it. – bobobobo May 13 '10 at 05:18
@bobobobo - How is your code going to determine "middle of the list" in order to do the insert? I still contend that this would significantly easier in the middle-tier than in the database. Your site code could store the values in an array and simply move items in the array given the particular sort. – Thomas May 13 '10 at 14:29

score 0 · Answer 5 · answered May 13 '10 at 10:08

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

@Thomas: correct and it does not support the (non-standard) [] as identifier quotes — , Feb 13 '11 at 23:39

score 0 · Answer 6 · answered May 13 '10 at 11:25

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.

score 0 · Answer 7 · answered May 14 '10 at 06:04

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

score 0 · Answer 8 · answered Feb 13 '11 at 23:33

0

If the column simply specifes the order of the rows, I don't see anything wrong with using 0 and negatives.

answered Feb 13 '11 at 23:33

Andriy M

76,112
17
94
154

Update a list of things without hitting every entry

8 Answers8

Linked