in reply to Re: Re: (OT?) Recursive sql queries?
in thread (OT?) Recursive sql queries?

Hrm, that looks interesting, the sql is certainly nice and simple enough, but I have to wonder, how do you maintain it? What happens if, after you've got all your nice lft and rgt columns set up, you want to insert a new node someplace?

It's not hard. There previously mentioned Intelligent Enterprise article covers one possible method. It's worth a read. You can do lots of useful things (aggregate reports, deleting subtrees, etc.) very quickly over in SQL land without having to bring stuff out of the database and munge it into a hierarchy on the Perl side.

After thinking about it for a bit, I think the biggest win is just to store 2 ids, an "ultimate parent" and a "sub parent", then I can just get all my nodes in one query and munge them in to a tree in perl.

This, of course, depends on your definition of "biggest win" ;-)

This way you have to have an id for every level of subtree that you want to refer to which means a change to the DB schema if you change the hierarchy.

Replies are listed 'Best First'.
Re: Re^3: (OT?) Recursive sql queries?
by BUU (Prior) on Feb 27, 2004 at 19:52 UTC
    Well, the accessing sql is refreshingly simple, but the updating code 
    BEGIN
DECLARE right_most_sibling INTEGER;

SET right_most_sibling
    = (SELECT rgt
         FROM Personnel
        WHERE emp = :your_boss);

UPDATE Personnel
   SET lft = CASE WHEN lft > right_most_sibling
                  THEN lft + 2
                  ELSE lft END,
       rgt = CASE WHEN rgt >= right_most_sibling
                  THEN rgt + 2
                  ELSE rgt END
 WHERE rgt >= right_most_sibling;

INSERT INTO Personnel (emp, lft, rgt)
VALUES ('New Guy', right_most_sibling,
                  (right_most_sibling + 1))
END;

    [download]
    Is rather scary and I'm not even sure my database (mysql) supports those.
[reply]
[d/l]
      This translates to MySQL, tested on 4.0.18: 
       SELECT @right_most_sibling := rgt
   FROM Personnel
  WHERE emp = ? /* :your_boss */;

 UPDATE Personnel
    SET lft = IF(lft >  @right_most_sibling, lft + 2, lft),
        rgt = IF(rgt >= @right_most_sibling, rgt + 2, rgt)
  WHERE rgt >= @right_most_sibling;

 INSERT INTO Personnel (emp, lft, rgt)
 VALUES ('New Guy', @right_most_sibling, @right_most_sibling + 1);

      [download]
      I don't know what version of MySQL introduced the @var := doohickey. Be warned that inserts etc. into a nested-set must touch all nodes to the right of the parent. That shouldn't be a problem if the tree is small or updated infrequently. A smarty might be able to devise a method to mitigate the change, but I'm not so smart, I plan to brute force a solution one of these days. :-) This nested-set thread has piqued my interest in the subject.
[reply]
[d/l]

In Section Seekers of Perl Wisdom