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

In article <c0d87ec0.0209200709.5e80bd94_at_posting.google.com>, "--CELKO--" <71062.1056_at_compuserve.com> wrote:

> Actually, you only need the lft_offset value, since the rgt_offset can
> be calculated by using the (lft, rgt) spread within the node.
>
>
Yes, you're right, and I think I screwed up the rgt summation as well. No need to add an extra term at each level, as I did below :-).

> rgt =
> (select sum(x.rgt_offset) from siblingoffset x
> ....same....)
>
>
The correct form would be sum(x.lft_offset) + (n.rgt - n.lft).

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

Yes, that makes some sense. The combined predicate for the summation terms would be something like "for node n, every node xx to the left of a node a on the path from root to n, inclusive". Summing the xx's spreads would yield the total offset from root.

        a=root
       /  |  \
     xx  xx   a
            / | \
           xx xx a
                / \
               xx  a=n

All the xx's are summed to get the total offset down to n.

select n.node_id, sum(1 + xx.rgt - xx.lft) + count(distinct a.node_id) totaloffset from

   orgchart n,
   orgchart a, /* ancestor from root to n inclusive */    orgchart xx /* left sibling of an a */ where n.lft >= a.lft and n.lft < a.rgt
and xx.parent_id = a.parent_id
and xx.rgt < a.lft
group by n.node_id

That count(distinct a) term is there to add 1 for each "a" node as we go down. Received on Fri Sep 20 2002 - 21:00:03 CEST