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

Date: Fri, 20 Sep 2002 12:00:03 -0700

Message-ID: <pan.2002.09.20.11.59.57.697.11608_at_yah00.com>

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