Re: Nested set model with large gaps and spreads in the numbering

From: --CELKO-- <71062.1056_at_compuserve.com>
Date: 20 Sep 2002 08:09:22 -0700
Message-ID: <c0d87ec0.0209200709.5e80bd94_at_posting.google.com>


>> It could be possible to do this with one sql statement, but it's
difficult. <<

Yes, very difficult <g>. So far, the shortest answer involves a user defined recursive function that builds the total offset when it is called.

>> So, the offset for a given node n from its parent's lft is

      1+ sum( 1+ leftsibling.rgt - leftsibling.lft )  where leftsibling.parent_id = n.parent_id and leftsibling.rgt < n.lft.  

 A view for the same:

 create view siblingoffset( node_id int, lft int, rgt int,

      lft_offset, rgt_offset )
 as

     select
         n.node_id, n.lft, n.rgt,
         1 + sum( 1+ leftsibling.rgt - leftsibling.lft ), 
         1 + sum( 1+ leftsibling.rgt - leftsibling.lft ) + (n.rgt -
n.lft)
 from

     orgchart n, orgchart leftsibling
 where n.parent_id = leftsibling.parent_id and leftsibling.rgt < n.lft; <<

Actually, you only need the lft_offset value, since the rgt_offset can be calculated by using the (lft, rgt) spread within the node.

>> Given the sibling offsets, it's a matter of summing them for all
nodes x
in the path from root, ie the usual nested sets predicate, where x.lft < n.lft and x.rgt > n.rgt. The sum is the offset from root's lft value (1
 or 0).  

 update orgchart

    set lft =
   (select sum(x.lft_offset)

      from siblingoffset AS x
     where orgchart.lft >= x.lft 
       and orgchart.rgt <= x.rgt),

   rgt =
   (select sum(x.rgt_offset) from siblingoffset x      ....same....)  

 The update could be more elegant, but this form is fairly clear. <<

I think that this looks pretty clear, too. I think we could change the VIEW to show the total of the offsets rather than doing the summation inside the scalar subqueries in the UPDATE, but I will have to play around a bit. I am assuming the VIEW would be materialized once. Received on Fri Sep 20 2002 - 17:09:22 CEST

Original text of this message