Geek Answers Handbook

How to define a "nesting level" in a table built as an adjacency list model

I have this table where TT_PLAN_TASK_ID has its parent id in TT_GROUP_ID:

enter image description here

This presents the tree data as follows:

enter image description here

I want to sort table data in tree order.
I thought that if you can calculate the "nesting level" as indicated, then it is as simple as ordering the nesting level + tt_fromdate.

Thoughts / Requirements:

  • The number of levels is not limited. For a limited number of levels, I could just do a few repeated joins in the same table . This approach also looked good, but again for limited depth.

  • I cannot use stored procedures (then calculating the level of nesting would be simple)

  • Ultimately, this should work on Firebird, MS SQL, Oracle. Oracle CONNECT BY seems to be an option, but that does not solve it for the other two.

  • When group nodes are at the same level and their start dates are equal, their order does not matter (identifiers 225 and 226 in the tree view start from 28-4-2012, 226 can appear before or after 225)

  • Speed ​​is not important, it is for a one-time conversion, and I do not expect clients to have more than 20 levels

  • [edit] I just notice that my second image should count levels 0 1 2 3, not 0 1 2 3 4; -)

How can I calculate this nesting level?
Or where did it come from: how can I sort by tree?

+4
3

. , tsql (sql-server). , , .

WITH tmpCTE (all_other_fields, TT_PLAN_TASK_ID, TT_GROUP_ID, [level]) as
(

    SELECT all_other_fields, TT_PLAN_TASK_ID, TT_GROUP_ID, 0 as [level]
    FROM #myTable
    WHERE TT_GROUP_ID = 0

    UNION ALL

    SELECT t.all_other_fields, t.TT_PLAN_TASK_ID, t.TT_GROUP_ID, [level] + 1
        FROM #myTable t
            INNER JOIN tmpCTE cte
                ON t.TT_GROUP_ID = cte.TT_PLAN_TASK_ID

)

SELECT * FROM tmpCTE order by level

, , , , . - " ", "" .

PARENT - A
 child 1
 child 4
 child 9

PARENT - B
 child 2
 child 3

" 2" " 3" " 4" " 9" - node. , , .

. , , . , , , , .

+2

, , .

, . ( 0- 1, 1- 2) ( 0- 2), , , .

, . , . , - .

+1

[OP:] , - , ( ).

Delphi, TClientDataset, :

  • (fkInternalCalc) TT_LEVEL (int), TT_DATEORDER (int), TT_SORTSTRING ()

  • , , , TT_LEVEL

  • TT_LEVEL, TT_FROMDATE, TT_PLAN_TASK_ID, TT_DATEORDER 0,1,... TT_PLAN_TASK_ID - , .

  • TT_LEVEL, TT_DATEORDER. . + TT_SORTSTRING. + dateorder TT_SORTSTRING. - "00 -00-01-00-02-01", ( ) " 2, 1" 1, 0 0, 0'. , , .

  • TT_SORTSTRING, TT_TASKORDER 0,1,2...

  • 3

, .
, , , "" " ", , .
, , 4, .

[]
: , , , ;-) " , , ". ( 3), .

0

More articles:


All Articles