Databases
StackOverflow2013
Postsdbo
POMySQL update a sorting index column to move items
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POMySQL update a sorting index column to move items
    text
    copied!<p>If I have the following table &amp; data to allow us to use the <code>sort_index</code> for sorting:</p> <pre><code>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); </code></pre> <p>I want to be able to do the following in the most efficient manner:</p> <ol> <li>Move a foo entry to a given position (scoped by the bar_id) <ul> <li>Ensure that the <code>sort_index</code> is always 1 indexed <strong>and has no gaps</strong></li> <li>You should be able to move items to the beginning and end of the list and rule #2 should still be applied</li> <li>It should be done entirely in queries and as few as possible (as the sets could be very large and looping over them doing individual <code>UPDATE</code>s is not ideal)</li> </ul></li> </ol> <p>To clarify what I'm trying to do, lets assume the table was empty so we have the following data:</p> <pre><code>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 </code></pre> <p>Then if we were to do the following moves</p> <ul> <li>foo 1 to sort_index 3</li> <li>foo 7 to sort_index 1</li> <li>foo 5 to sort_index 5</li> </ul> <p>We should get the following data:</p> <pre><code>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 </code></pre> <p>And <code>SELECT * FROM foo ORDER BY bar_id, sort_index;</code> gives us:</p> <pre><code>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 </code></pre>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload