Updating display order of multiple MySQL rows in one or very few queries
Solution 1
soulmerge's answer made me think and I think this is a better solution. What you need to do is select the rows with the id using IN() and then use CASE to set the value.
UPDATE mytable SET display_order =
CASE id
WHEN 10 THEN 1
WHEN 23 THEN 2
WHEN 4 THEN 3
END CASE
WHERE id IN (10, 23, 4)
I have a need for this in my current app. In PHP, I'm getting a serialized (and ordered) set of id's from jQuery UI's built-in Sortable feature. So the array looks like this:
$new_order = array(4, 2, 99, 15, 32); // etc
To generate the single MySQL update query, I do this:
$query = "UPDATE mytable SET display_order = (CASE id ";
foreach($new_order as $sort => $id) {
$query .= " WHEN {$id} THEN {$sort}";
}
$query .= " END CASE) WHERE id IN (" . implode(",", $new_order) . ")";
The "implode" at the end just gives me my ID list for IN(). This works beautifully for me.
Solution 2
You should first ensure that the column has no UNIQUE index, otherwise mysql will tell you that the constraint is broken during the query. After that you can do things like:
-- Move #10 down (i.e. swap #10 and #11)
UPDATE mytable SET display_order =
CASE display_order
WHEN 10 THEN 11
WHEN 11 THEN 10
END CASE
WHERE display_order BETWEEN 10 AND 11;
-- Move #4 to #10
UPDATE mytable SET display_order
CASE display_order
WHEN 4 THEN 10
ELSE display_order - 1
END CASE
WHERE display_order BETWEEN 4 AND 10;
But you should actually ensure that you do things in single steps. swapping in two steps will result in broken numbering if not using ids. i.e.:
-- Swap in two steps will not work as demostrated here:
UPDATE mytable SET display_order = 10 WHERE display_order = 11;
-- Now you have two entries with display_order = 10
UPDATE mytable SET display_order = 11 WHERE display_order = 10;
-- Now you have two entries with display_order = 11 (both have been changed)
And here is a reference to the CASE statement of mysql.
Solution 3
A little late, but it may be useful to someone else:
UPDATE mytable SET display_order = FIND_IN_SET(rowId, '1,9,2,6,23') WHERE rowId in (1,9,2,6,23)
Solution 4
You could try to wrap it into a few statements, I don't think it's possible in a single one. So for example, let's say you are going to update the 10th row. You want every record after 10 to be bumped up.
UPDATE table SET col=col+1 WHERE col > 10
UPDATE table SET col=10 WHERE id = X
...
But it's really tough to roll in all logic required. Because some records maybe need a decrement, etc.. You want to avoid duplicates, etc..
Think about this in terms of developer time vs. gain.
Because even if someone sorts this once per day, the overhead is minimal, compared to fixing it in a stored procedure, or pseudo-optimizing this feature so you don't run 20 queries. If this doesn't run 100 times a day, 20 queries are perfectly fine.
Solution 5
If you need to drag you rows, this is a good implementation for a linked list
.
Having your rows ordered with a linked list
means that you will update at most 3
rows at a time -- even if you move the whole block (as long as it's contiguous).
Create a table of your rows like this:
CREATE TABLE t_list (
id INT NOT NULL PRIMARY KEY,
parent INT NOT NULL,
value VARCHAR(50) NOT NULL,
/* Don't forget to create an index on PARENT */
KEY ix_list_parent ON (parent)
)
id parent value
1 0 Value1
2 3 Value2
3 4 Value3
4 1 Value4
and use this MySQL
query to select the rows in order:
SELECT @r := (
SELECT id
FROM t_list
WHERE parent = @r
) AS id
FROM (
SELECT @r := 0
) vars,
t_list
This will traverse your linked list and return the ordered items:
id parent value
1 0 Value1
4 1 Value4
3 4 Value3
2 3 Value2
To move a row, you'll need to update the parent
of the row, the parent
of its current child, and the parent
of the row you're inserting before.
See this series of articles in my blog on how to do it efficiently in MySQL
:
- Sorting lists - how to select ordered items from a linked list
- Sorting lists: moving items - how to move a single item
- Sorting lists: adding items - how to insert a single item
- Sorting lists: deleting items - how to delete a single item
- Sorting lists: moving blocks - how to move a contiguous block
- Sorting lists: deleting blocks - how to delete a contiguous block
There are lots of articles because there are some issues with row locking which should be handled slightly differently for each case.
Related videos on Youtube
Peter Craig
I'm a unicycling acrobat, artist, collector and recreational programmer. Dabbling in web, plugin and app development, framework tinkering, UX design, with a lean toward WordPress plugins and Google APIs (Google Maps, Analytics, AdWords). A little obsessed with statistics, marketing, behaviour and predictive data.
Updated on April 26, 2022Comments
-
Peter Craig about 2 years
I have a table with say 20 rows each with a number for display order (1-20).
SELECT * FROM `mytable` ORDER BY `display_order` DESC;
From an admin area you can drag the rows around or type a new number manually for each row.
Surely it's not good to loop over an UPDATE query for every row, what's an alternative in one or very few queries suitable for updating one cell in 20 rows or even more, 50-200+?
Edit: A lot of good responses and ideas. I might expand on the ideas I've considered so far:
One array string: I could have the order in a string listing the unique row IDs in the order I want - eg rows 1,9,2,6,23. When the order is updated, a hidden field updates with JavaScript and adds that to the database or a text file when complete:
UPDATE `my_dispaly_order_table` SET `display_order`='1,9,2,6,23';
Update each row individually: This is what I was trying to avoid but it would only be changed very infrequently so 20-30 calls in one hit once a week or month might not be a problem so simply calling UPDATE on each row is what I usually do:
UPDATE `mytable` SET `display_order`='1' WHERE `rowId` = 1; UPDATE `mytable` SET `display_order`='2' WHERE `rowId` = 9; UPDATE `mytable` SET `display_order`='3' WHERE `rowId` = 2; UPDATE `mytable` SET `display_order`='4' WHERE `rowId` = 6; UPDATE `mytable` SET `display_order`='5' WHERE `rowId` = 23;
-
Peter Craig almost 15 yearsI guess two issues with this is you still may need to update a few fields but at least not all... it might get a little hairy after time! I had a similar concept but started in units of 100 so it at least had ints instead of decimals but not too different... I'd say you'd still want to update the order every now and then to be normal consistent numbers. Thanks for the contribution.
-
Peter Craig almost 15 yearsThis seems very complicated... my main thought is it's adding more work instead of updating the order of one item it updates 3 fields for every order change...
-
Peter Craig almost 15 yearsIt may not work so well for frequent sorting so you keep adding or shifting items to the top (news items for example) so you'd end up with 0.1, 0.0001 - but then I guess it can go into the thousands... although it seems a slightly left-field idea I'm actually leaning towards this solution...
-
Quassnoi almost 15 years3 fields instead of 1 isn't a problem in 2009 :) If you will have more than 1,000 rows and frequent updates, the linked list it the best decision, as it's always 3 fields no matter what. Other solutions will require either updating all fields or a binary sort which is likely to run out of values.
-
Xeoncross about 12 yearsI've been hitting my head against my own
UNIQUE
index trying to use PHP to solve this logic problem. I should just remove that index like you said! I was just worried about introducing errors into the database. -
Kumar over 11 yearsThis is clever! I fire an update even when the list is ordered or an element is deleted from the list, just one query.
-
Andrew almost 11 yearsI don't think the syntax is correct here. For example, it should be simply "END" and not "END CASE".
-
Jamon Holmgren almost 11 yearsFor MySQL, you need to use
END CASE
. Ref: dev.mysql.com/doc/refman/5.7/en/case.html -
Riccardo over 10 yearsFor MySQL you have to use
END CASE
for CASE statement andEND
for CASE expression Statement: dev.mysql.com/doc/refman/5.7/en/case.html Expression: dev.mysql.com/doc/refman/5.7/en/… -
Crezzur over 2 yearsThis code line did the job for me ;-)