MySQL updates sort index column to move items

Question

If I have the following table and data that allows us to use sort_index for sorting:

 CREATE TABLE `foo` ( `id` INT(11) NOT NULL AUTO_INCREMENT, `bar_id` INT(11) DEFAULT NULL, `sort_index` INT(11) DEFAULT NULL, PRIMARY KEY (`id`) ); INSERT INTO `foo` (`bar_id`, `sort_index`) VALUES (1,1),(1,2),(1,3),(1,4), (2,1),(2,2),(2,3),(2,4),(2,5); 

I want to be able to do the following in the most efficient way:

• Move foo entry to specified position (bar_id scope)
  • Make sure sort_index always 1 indexed and has no spaces
  • You should be able to move items to the beginning and end of the list, and rule number 2 should be applied
  • This should be done completely in the queries and as small as possible (since the sets can be very large and loop into them, making individual UPDATE not ideal)

To clarify what I'm trying to do, let's say that the table is empty, so we have the following data:

 id | bar_id | sort_index 1 | 1 | 1 2 | 1 | 2 3 | 1 | 3 4 | 1 | 4 5 | 2 | 1 6 | 2 | 2 7 | 2 | 3 8 | 2 | 4 9 | 2 | 5 

Then, if we performed the following movements

• foo 1 for sort_index 3
• foo 7 for sort_index 1
• foo 5 for sort_index 5

We should get the following data:

 id | bar_id | sort_index 1 | 1 | 3 2 | 1 | 1 3 | 1 | 2 4 | 1 | 4 5 | 2 | 5 6 | 2 | 2 7 | 2 | 1 8 | 2 | 3 9 | 2 | 4 

AND SELECT * FROM foo ORDER BY bar_id, sort_index; gives us:

 id | bar_id | sort_index 2 | 1 | 1 3 | 1 | 2 1 | 1 | 3 4 | 1 | 4 7 | 2 | 1 6 | 2 | 2 8 | 2 | 3 9 | 2 | 4 5 | 2 | 5 

Answer 1

You should be able to do this in one query: something along the UPDATE foo SET sort_index = sort_index + 1 WHERE bar_id == b AND sort_index < s1 AND sort_index >= s2 lines UPDATE foo SET sort_index = sort_index + 1 WHERE bar_id == b AND sort_index < s1 AND sort_index >= s2 , where b is the bar_id line to be moved, s1 - this is the current sort_index this line, and s2 is the sort_index you want to move it to. Then you just change the sort_index string.

You probably want to execute two queries inside a transaction. Also, this can speed things up if you created an index on sort_index using something like CREATE INDEX foo_index ON foo (sort_index) .

(By the way, here I assume that you do not want to duplicate the sort_index values ​​within the given bar_id and that the relative order of the lines should never be changed, except explicitly. If you do not need this, the solution is even simpler.)