Re: Nested set model with large gaps and spreads in the numbering
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