I have a table like so:
categoryID categoryName
----------------------------
1 A
2 B
3 C
Now I want the user to be able to order this data according to his will. I want to remember his preferred order for future. So I thought I'd add a column order to the table above and make it of type INT and AUTO_INCREMENT. So now I get a table like this:
categoryID categoryName order
-------------------------------------
1 A 1
2 B 2
3 C 3
4 D 4
My problem is - the user now decides, to bring categoryName with order 4 (D in example above) up to 2 (above B in example above) such that the table would now look like:
categoryID categoryName order
-------------------------------------
1 A 1
2 B 3
3 C 4
4 D 2
My question is - How should I go about assigning new values to the order column when a reordering happens. Is there a way to do this without updating all rows in the table?
One approach that comes to mind is to make the column a FLOAT and give it an order of 1.5 if I want to bring it between columns with order 1,2. In this case I keep loosing precision as I reorder items.
EDIT:
Another is to update all rows between (m, n) where m, n are the source and destination orders respectively. But this would mean running (m-n) separate queries wouldn't it?
Edit 2:
Assuming I take the FLOAT approach, I came up with this sql to compute the order value for an item that needs to be inserted after item with id = 2 (for example).
select ((
select `order` as nextHighestOrder
from `categories`
where `order` > (
select `order` as targetOrder
from `categories`
where `categoryID`=2)
limit 1) + (
select `order` as targetOrder
from `categories`
where `categoryID`=2)) / 2;
This gives me 3.5 which is what I wanted to achieve.
Is there a better way to write this? Notice that select order as targetOrder from categories where categoryID=9 is executed twice.
解决方案
If the number of changes is rather small you can generate a clumsy but rather efficient UPDATE statement if the you know the ids of the involved items:
UPDATE categories
JOIN (
SELECT 2 as categoryID, 3 as new_order
UNION ALL
SELECT 3 as categoryID, 4 as new_order
UNION ALL
SELECT 4 as categoryID, 2 as new_order) orders
USING (categoryId)
SET `order` = new_order;
or (which I like less):
UPDATE categories
SET `order` = ELT (FIND_IN_SET (categoryID, '2,3,4'),
3, 4, 2)
WHERE categoryID in (2,3,4);
UPD:
Assuming that you know the current id of the category (or its name), its old position, and its new position you can use the following query for moving a category down the list (for moving up you will have to change the between condition and new_rank computation to rank+1):
SET @id:=2, @cur_rank:=2, @new_rank:=4;
UPDATE t1
JOIN (
SELECT categoryID, (rank - 1) as new_rank
FROM t1
WHERE rank between @cur_rank + 1 AND @new_rank
UNION ALL
SELECT @id as categoryID, @new_rank as new_rank
) as r
USING (categoryID)
SET rank = new_rank;