Re: Hierarchical data and sorting

From: --CELKO-- <jcelko212_at_earthlink.net>
Date: 30 Jun 2004 12:50:57 -0700
Message-ID: <18c7b3c2.0406301150.1c1f7e71_at_posting.google.com>

>> Does your book answer my question? <<

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

1. If (i BETWEEN i0 AND i1) then (i) should be updated to (i + i3- i1).
2. If (i BETWEEN i2 AND i3 then (i) should be updated to (i + i0 - i2).
3. 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)) LANGUAGE SQL
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);

UPDATE Tree

   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 END

 WHERE 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