Finding the most suitable node materialized path tree

Question

Is it possible to sort a pathtext field by textured tree tree tree tree to find the rightmost node of the tree? For example, consider this python function that uses django-treebeard MP_Node:

def get_rightmost_node():
    """Returns the rightmost node in the current tree.

    :rtype: MyNode
    """
    # MyNode is a subclass of django-treebeard MP_Node.
    return MyNode.objects.order_by('-path').first()

From all my testing, it seems that what I expect is returning, but I don’t know how to come up with math to prove it. And I did not find any information about this operation in the materialized path tree.

Implementation of Treebeard does not separators in the path, so the path is as follows: 0001, 00010001, 000100010012etc.

Answer 1

: .

SQLFiddle, , .

:

id, path
1,  '1'
2,  '1\2'
3,  '1\3'
4,  '1\4'
5,  '1\5'
6,  '1\6'
7,  '1\7'
8,  '1\8'
9,  '1\9'
10, '1\10'

(id = 10) :

SELECT TOP 1
  id,
  path
FROM hierarchy
ORDER BY path DESC

:

id, path
9,  1\9

path , 1\10 1\9 (. ).

, , , , :

path       depth  length
12\3\11\2  4      9
5\17\10\1  4      9

- .

, 26- 10-:

SQLFiddle

, , django, , , , , - path, .

EDIT - , , :

- " " - , (.. ). a node 10 , , ? , .

- " " , , node :

        1
       / \
    1\1   1\2 <= This is the rightmost node
    /
  1\1\1 <= This is the lowest node

- " ", node:

//in pseudocode
function GetRightmostNode(Node startNode)
{
  Node currentNode = startNode;

  while(currentNode.RightChildren != null)
  {
    currentNode = maximum of currentNode.RightChildren;
  }

  return currentNode;
}

node node. , . node - , node, node ( ) startNode .

Answer 2

, node ?

. , , node '/1/3/6/2', :

/1
/1/3
/1/3/6/2
/1/3/6/5
/1/3/6/21
/1/40

, .

. " node", , , . :

select length(regexp_replace('/1/3/6/2', '[^/]+', '', 'g')) as depth;

, - :

order by length(regexp_replace(path, '[^/]+', '', 'g')) desc

... Python. .

- , , . , , , , (1, 11, 2) (1, 11, 2):

select regexp_replace('/1/3/6/2', '^.+/', '')::int as value;

Answer 3

EDIT: , , , . , .

, , node, :

SELECT path, 
       length(regexp_replace(path, '[^/]+', '', 'g')) as depth,
       regexp_replace(path, '^.*/', '')::int as last       
FROM test 
ORDER BY depth DESC, last DESC;

node .

SQLFiddle

Answer 4

@Paul . 0 .

,

id |  path
-----------------
1  |  '01'
2  |  '01\01'
3  |  '01\02'
4  |  '01\03'
5  |  '01\04'
6  |  '01\04\01'
7  |  '01\04\02'
8  |  '01\04\03'
9  |  '01\05\01'
10 |  '01\05\02'
11 |  '01\05\03'
12 |  '01\05\04'

, node 100.

100 1000, 0 001\003\002\005 .

node 12 ,

SELECT TOP 1 id
FROM tree
ORDER BY path DESC

.