Nested set model: Difference between revisions

Revision as of 23:08, 21 September 2019

Revision as of 11:51, 25 July 2020

Line 100:

Using the nested set model as described above has some performance limitations during certain tree traversal operations. For example, trying to find the immediate child nodes given a parent node requires pruning the subtree to a specific level as in the following [[SQL]] code example:

<sourcesyntaxhighlight lang="sql">

SELECT Child.Node, Child.Left, Child.Right

FROM Tree as Parent, Tree as Child

Line 113:

)

AND Parent.Left = 1 -- Given Parent Node Left Index

</syntaxhighlight>

</source>

Or, equivalently:

<sourcesyntaxhighlight lang="sql">

SELECT DISTINCT Child.Node, Child.Left, Child.Right

FROM Tree as Child, Tree as Parent

Line 121:

GROUP BY Child.Node, Child.Left, Child.Right

HAVING max(Parent.Left) = 1 -- Subset for those with the given Parent Node as the nearest ancestor

</syntaxhighlight>

</source>

The query will be more complicated when searching for children more than one level deep. To overcome this limitation and simplify [[tree traversal]] an additional column is added to the model to maintain the depth of a node within a tree.

Line 154:

In this model, finding the immediate children given a parent node can be accomplished with the following [[SQL]] code:

<sourcesyntaxhighlight lang="sql">

SELECT Child.Node, Child.Left, Child.Right

FROM Tree as Child, Tree as Parent

Line 162:

AND Child.Right < Parent.Right

AND Parent.Left = 1 -- Given Parent Node Left Index

</syntaxhighlight>

</source>

==See also==