I'm trying to create the most efficient way to allow a user to change 
the display order of a group of rows in a table.
Lets say the basic table is:
id
group_id
name
sort_order
The query to display it would be
"SELECT id, name FROM mytable WHERE group_id = $x ORDER BY sort_order"
Now when I display it they currenlty all have the same sort_order value
so they come in the order however the db finds them.
In my PHP app... I have a small arrow that allow them to move a row up
or down changing the display order.
Currently this is done by looping through the results of all the items
in a group and reassigning a new sort_order value to each one.
Meaning 1 SELECT and MANY updates (1 for each item in the group).
I was wondering if anyone has come up with a better way to do this.
Thanks,
Mike
Michael J. Pawlowsky 's gravatar image asked Nov 29 2004 at 16:17 in Mysql-General by Michael J. Pawlowsky

3 Answers

I wouldn't use a loop but an UPDATE statement instead.
If I understand you correctly, all of your records are in the same group
but you need them displayed in a user-defined order. It would be MUCH
easier to manage that if the sort order values were already in sequential
order. You may need to do a one-time loop-based query to individually
reset each row to an appropriate value but after that these UPDATE
statements should keep everything in order.
Assume you want to move an item of group 6 from position 2 to position 6
SELECT @id := id from basictable where group_id = 6 and sort_order = 2;
UPDATE basictable
SET sort_order = if (id = @id, 6, sort_order -1)
WHERE group_id = 6
AND sort_order BETWEEN 2 AND 6;
and going the other way, from position 10 to position 2
SELECT @id := id from basictable where group_id = 6 and sort_order = 10;
UPDATE basictable
SET sort_order = if (id = @id, 2, sort_order +1)
WHERE group_id = 6
AND sort_order BETWEEN 2 AND 10;
make sense?
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
"Michael J. Pawlowsky" wrote on 11/29/2004 11:17:01
AM:
SGreenunimin.com
Date: Mon, 29 Nov 2004 11:40:07 -0500




I wouldn't use a loop but an UPDATE statement instead.

If I understand you correctly, all of your records are in the same group 
but you need them displayed in a user-defined order. It would be MUCH 
easier to manage that  if the sort order values were already in sequential 
order. You may need to do a one-time loop-based query to individually 
reset each row to an appropriate value but after that these UPDATE 
statements should keep everything in order.

Assume you want to move an item of group 6 from position 2 to position 6

SELECT id := id from basictable where group_id = 6 and sort_order = 2;

UPDATE basictable 
SET sort_order = if (id = id, 6, sort_order -1) 
WHERE group_id = 6 
        AND sort_order BETWEEN 2 AND 6;


and going the other way, from position 10 to position 2

SELECT id := id from basictable where group_id = 6 and sort_order = 10;

UPDATE basictable 
SET sort_order = if (id = id, 2, sort_order +1) 
WHERE group_id = 6 
        AND sort_order BETWEEN 2 AND 10;

make sense?

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine


Michael J. Pawlowsky 's gravatar image answered Nov 29 2004 at 16:40 by SGreenunimin.com Date: Mon, 29 Nov 2004 11:40:07 -0500 I wouldn't use a loop but an UPDATE statement instead. If I understand you correctly, all of your records are in the same group but you need them displayed in a user-defined order. It would be MUCH easier to manage that if the sort order values were already in sequential order. You may need to do a one-time loop-based query to individually reset each row to an appropriate value but after that these UPDATE statements should keep everything in order. Assume you want to move an item of group 6 from position 2 to position 6 SELECT id := id from basictable where group_id = 6 and sort_order = 2; UPDATE basictable SET sort_order = if (id = id, 6, sort_order -1) WHERE group_id = 6 AND sort_order BETWEEN 2 AND 6; and going the other way, from position 10 to position 2 SELECT id := id from basictable where group_id = 6 and sort_order = 10; UPDATE basictable SET sort_order = if (id = id, 2, sort_order +1) WHERE group_id = 6 AND sort_order BETWEEN 2 AND 10; make sense? Shawn Green Database Administrator Unimin Corporation - Spruce Pine Michael J. Pawlowsky
Here is one idea: The sort_order column could be an integer, you could
put values 1,2,3... for each group in this column. When a user move a
row down, you issue statements similar to this:
SELECT @so:=sort_order FROM mytable WHERE id = $moving_id;
UPDATE mytable SET sort_order = sort_order + 1 WHERE id = $moving_id;
UPDATE mytable SET sort_order = sort_order - 1
WHERE group_id=$x AND [email protected] + 1 AND id != $moving_id;
This assumes you only know the group_id $x and the $moving_id. The "@so"
is a user variable, it wont work well with replication. You can easily
do the same in your application. If you also know the id of the row you
are "swapping" with, its easier:
UPDATE mytable SET sort_order = sort_order + 1 WHERE id = $moving_id;
UPDATE mytable SET sort_order = sort_order - 1 WHERE id = $other_id;
It would be equally easy to swap any two items in the list, not just two
adjecent items. Just swap sort_order values.
To move any item to the top, you would need something like this:
SELECT @so:=sort_order FROM mytable WHERE id = $moving_id;
UPDATE mytable SET sort_order = 1 WHERE id = $moving_id;
UPDATE mytable SET sort_order = sort_order + 1
WHERE group_id=$x AND sort_order<@so and id != $moving_id;
In this case all rows between the moving row and the row it is moving to
needs to be changed. This is heavier on the db, but it should not be an
issue, unless your users do this "all the time". You should in any case
index the table non-uniquely on (group_id,sort_order), in addition to
the primary key on id.
Roger
Roger Baklund 's gravatar image answered Nov 29 2004 at 16:57 by Roger Baklund
Yup... I think that is the key!
Basically as they are inserted I will look up the max sort_order value
for that group so far and increase that by one for the current insert.
Then when it come to changing the sort order values I will simply swap
it with the value of the previous or next one.
So only 2 updates.
The only thing I need to look out for is not to allow them to try and
move the first row up... or the last row down.
Thanks for the help,
Mike
Michael J. Pawlowsky 's gravatar image answered Nov 29 2004 at 17:32 by Michael J. Pawlowsky