[thelist] Interesting SQL Puzzle [SOLUTION]
Frank
lists at frankmarion.com
Sun Aug 21 00:57:55 CDT 2005
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.
More information about the thelist
mailing list