[thelist] Interesting SQL Puzzle [SOLUTION]

[Frank]

I've solved my problem, thank you all for your advice. I'll re-iterate it 
for the archives.

I have a series of records (representing categories) that I want to rank 
numerically, with no gaps in the sequence of ranking orders and no 
collisions: I want to move a record from one position in a list to another, 
up, or down, in my case, with a select menu. Here are the basics of how 
I've accomplished it.

The (MySQL) database looks something like this

cat_id (pk)              cat_name.                  cat_rank*      parent_id**
133                        Sir Anthony Caro      1                    130
134                        e.e. cummings               2                    130
135                        Eric Gill           3                    130

* The rank is an unsigned INT, meaning no negative values allowed.

* I use parent_id as a foreign key to a "parent" record, as this is being 
used in a hierarchical tree structure.

The web page contains a form with a text field for the name (cat_name), a 
select menu populated with the contents of the rank field that ranges from 
1 to MAX() for the query (form.cat_rank)  and a hidden field with the 
current rank value of the record (form.current_rank).

When I insert a record, the rank is always set to MAX() + 1.  To do 
otherwise might most likely mess this whole scheme up. When I update my 
record, I perform the following (pseudo-code)

NOTE that when INCREASING the rank we ADD 1 to each record from LOWEST to 
HIGHEST then move the desired record with a second update. So, to move from 
position 10 in the list to position 1, we add 1 to records whose rank is 1 
to 9 (1+1=2, 5+1=6, 9+1=10) then we take the pk of our one record to move 
and set it's rank to what we've chosen in our select menu.

When DECREASING the rank, we SUBSTRACT1 from each record from the HIGHEST 
to the LOWEST them move our desired record with a second update. So, to 
move from position 1 in the list to position 10, we subtract one from 10 to 
2 (10-1=9, 5-1=4, 2-1=1) the we take the pk of our one record to move and 
make a separate and specific update.

Remember that MySQL's BETWEEN clause is INCLUSIVE, that means that BETWEEN 
1 AND 10 includes all 10 numbers, NOT 2 to 9. To work around this, when I 
use the BETWEEN operator, when I increase the rank, I increase between 1 
and my select menu value MINUS 1, because each number is being updated with 
+1. If I did not do this, I would end up with an 11. The reverse also 
applies, when decreasing a rank I substract 1 from my current rank, 
otherwise I might end up with a zero or negative number at the beginning of 
my list.

------------------------------------------------------------------------------------------------------

// [1] if there's a change in rank (no point in updating if rank remains 
the same)
   if cat_rank != current_rank {

   // [2] Raise the rank (from 10 to 1, for example)
      if form.current_rank GT form.cat_rank {

   // Add 1 to each number between the lowest and the highest (except the 
one to change
      UPDATE categories SET
      cat_rank = cat_rank + 1
      WHERE cat_rank BETWEEN (#form.cat_rank#) AND (#form.current_rank# -1)
      AND parent_id = #form.parent_id#;

   // Change the rank of the one we want to what we want
      UPDATE categories SET
      cat_rank = #form.cat_rank#
      WHERE cat_id = #form.cat_id#;


   // [2] Else, lower the rank (from 1 to 10, for example)
      } cfelse {

   // Add 1 to each number between the lowest and the highest (except the 
one to change)
      UPDATE categories SET
      cat_rank = cat_rank - 1
      WHERE cat_rank BETWEEN (#form.current_rank# +1) AND (#form.cat_rank#)
      AND parent_id = #form.parent_id#;


   // Change the rank of the one we want to what we want
      UPDATE categories SET
      cat_rank = #form.cat_rank#
      WHERE cat_id = #form.cat_id#;

   //[2] End if
      }

// [1] end if
   }


------------------------------------------------------------------------------------------------------

In the end, I used this in conjuction with a hiearchical tree, so now I can 
reorder my levels, or within any level, because my query is constrained by 
the parent_id.

I look forward to any efficiencies or elegances that you may come up with.



Frank Marion     lists at frankmarion.com      Keep the signal high.