Re: Nested Sets and custom sorting

From: Mark (mpluijmaekers_at_yahoo.com)
Date: 08/04/04 

Date: 4 Aug 2004 00:01:48 -0700

"Adam Machanic" <amachanic@hotmail._removetoemail_.com> wrote in message news:<Ov4rdOWeEHA.3016@tk2msftngp13.phx.gbl>...
> Mark,
>
> Is the data re-ordered at runtime (e.g. can the user specify an order),
> or is the ordering static at build time (when you create the tree)? If the
> latter, just insert the nodes into the tree in whatever order you'd like to
> retrieve them. If the former, it will probably be easier (and more
> efficient) to return the SortIndex to the client and sort the data there.

The ordering is static at the moment I (re-)build the menu. I tried to
insert the nodes into the tree in the correct order, but couldn't find
an elegant/fast solution to do this. The query that inserts a node
into the menu-table in Joe Celko's nested sets model looks like:

       -- push when top has subordinates and set lft value
       INSERT INTO #Stack
       SELECT (@current_top + 1), MIN(T1.Nr), @counter, NULL, @level
         FROM #Stack AS S1, @Tree AS T1
        WHERE S1.child = T1.Parent
          AND S1.stack_top = @current_top;

So in this case, the node with the lowest id (T1.Nr) is inserted first
into #Stack. In my case, the ordering of sub-nodes depends on the
value of the ListOrder and SortIndex fields of a node. And I can't
find a good way of inserting the nodes in the desired order without
using nested cursors or such. Anyone?

Relevant Pages

  • Generating binary and ternary trees in teh neste4d sets model
    ... There are many ways to represent a tree or hierarchy in SQL. ... define a simple OrgChart table like this. ... Is there a simple way to generate a nested sets model of a complete ...
    (comp.databases.theory)
  • Re: Reflexive table problem - tricky
    ... > can be done in a VIEW with the nested sets model. ... > FROM OrgChart AS E ... > tree as an indented listing. ... > constraints, Declarative Referential Integrity, datatypes, etc. in your ...
    (microsoft.public.sqlserver.programming)
  • Re: Self join - multiple rows in result.
    ... You ned to look at the nested sets model instead of this adjacency list ... model for a tree. ... constraints, Declarative Referential Integrity, datatypes, etc. in your ... schema are. ...
    (microsoft.public.sqlserver.programming)