# 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

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

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; <<

>> 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....)

Original text of this message