Changing Sort Order Of Items.

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

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

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 sort_order=@so + 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

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:

When items are sorted, are all the docs with the sort field missing considered "tied" in terms of their sort order, or are they "indeterminate", or do they have some arbitrary order imposed on them (e.g. _docid_)? For example, would "b" be considered as part of the sort in the following query, or would all the missing 'a' fields be in some kind of order already, thus making the sort algorithm never
this one bugs me for a while. how to change order. I have a list of tasks. by status, task could be 1 (todo) or 0 (done) - status value stored in mysql. I can list tasks per status or all. order number is stored in mysql too. the easiest way to change order is to have form for each task where you will enter manually number and then submit (one submit button for whole form). but, if you change
Hi, Anybody a great idea for changing item order in a sorted set using the wicket viewer? I have a rank-property in the database but how to easily manage the item order (move item(s) up/down single or multiple places)... Thanks, Erik
Hi, I need to sort a map however when more then 10 items the sort order takes 11 before 2, ie: 1,11,12,2,3,4,5 etc. I've tried sortWith(_._1.sort < _._1.sort) and sortBy(_.1.sort) but doesn't work Thanks
Is there any way to control introduce a different ordering scheme from the base comparable bytes? My use case is that I am using UTF-8 data for my keys, and I would like to have scans use UTF-8 collation. Could this be done by providing an alternate implementation of WritableComparable? Thanks in advance! --Tom
I know cachedump may be going away soon, but for now it's still a somewhat useful diagnostic tool. To that end, what determines the order of the keys returned by cachedump? Is it based on the LRU or just completely random? Thanks! You received this message because you are subscribed to the Google Groups "memcached" group. To unsubscribe from this group and stop receiving emails from
I have documents of the following structure: things: [   {     name: "one",     score: 5   },   {     name: "two",     score: 3   } ] my question is there any way to sort "things" array when I $inc "score" of one of the items in "things" ? thx.
Hello, Is it permissible to order a clause such that the search term is the first item (in the clause)? standard: field1 IN (123, 654, 789) in question: 123 IN (field1, field2, field3) I am interested to know if the optimizer treats this any differently if anybody can shed any light on it (except for the obvious difference in the above queries!) Thanks, Andy
I have a form that allows for an item to be entered, the other pieces have been fixed so far that were bogging me down, but now I am looking for a way to convert any entry in the form to be UPPER case so that when the quote is listed, they are alphabetical. The problem is if someone enters aa-1234 and the other items in the quote are FF-2345 and QQ-3456 then the aa-1234 is UNDER them, instead
I'm working on a little shopping cart script and when I get to the checkout portion I show what is in the cart and for each item I show the quanity, price per unit then I show the total cost for that item. So basically it looks like $total = $qty * $price My question is how do I add the value of $total to give the over total of the order? Since there may be one or more different items in