Re: Hierarchical data and sorting
Date: 30 Jun 2004 12:50:57 -0700
Yes, in a little over 200 pages :)
First you need to decide on a model to use for the hierarchy. If you use a nested set or nested interval model, you can re-arrange the order of the siblings to anything you wish. Here is the code for the nested sets model:
04.08.04. Swapping Siblings
The following solution for swapping the positions of two siblings under the same parent node is due to Mr. Vanderghast and originally appeared in a posting on the MS-SQL Server Newsgroup.
If the leftmost sibling has its (lft, rgt) = (i0, i1) and the other subtree, the rightmost sibling, has (i2, i3), implicitly, we know that (i0 < i1 < i2 < i3).
With a little algebra, we can figure out that if (I) is a lft or rgt value in the table between i0 and i3, then
- If (i BETWEEN i0 AND i1) then (i) should be updated to (i + i3- i1).
- If (i BETWEEN i2 AND i3 then (i) should be updated to (i + i0 - i2).
- If (i BETWEEN i1+1 AND i2-1) then (i) should be updated to (i0 + i3 + i - i2 -i1).
All of this becomes a single update statement, but we will put the (lft, rgt) pairs of the two siblings into local variables so a human being can read the code.
CREATE PROCEDURE SwapSiblings
(IN lft_sibling CHAR(2), IN rgt_sibling CHAR(2))
DETERMINISTIC BEGIN ATOMIC
DECLARE i0 INTEGER; DECLARE i1 INTEGER; DECLARE i2 INTEGER; DECLARE i3 INTEGER; SET i0 = (SELECT lft FROM Tree WHERE node = lft_sibling); SET i1 = (SELECT rgt FROM Tree WHERE node = lft_sibling);SET i2 = (SELECT lft FROM Tree WHERE node = rgt_sibling); SET i3 = (SELECT rgt FROM Tree WHERE node = rgt_sibling);
SET lft = CASE WHEN lft BETWEEN i0 AND i1
THEN i3 + lft - i1 WHEN lft BETWEEN i2 AND i3 THEN i0 + lft - i2 ELSE i0 + i3 + lft - i1 - i2 END, rgt = CASE WHEN rgt BETWEEN i0 AND i1 THEN i3 + rgt - i1 WHEN rgt BETWEEN i2 AND i3 THEN i0 + rgt - i2 ELSE i0 + i3 + rgt - i1 - i2 ENDWHERE lft BETWEEN i0 AND i3
AND i0 < i1 AND i1 < i2 AND i2 < i3;
END; This gives you the swap function for a sort procedure of your choice.
Now, go buy the book :) It's full of good stuff like this! Received on Wed Jun 30 2004 - 21:50:57 CEST