2

I have read and used http://www.mysqlperformanceblog.com/2011/02/14/moving-subtrees-in-closure-table/ and http://www.slideshare.net/billkarwin/models-for-hierarchical-data, but does not solve my problem.

My question is:

How is it possible to maintain a integer column which describes how a given node is ordered with respect to other nodes of same depth in a closure table structure?

Currently, I am using the following sql statement to insert new tree nodes which is working as expected:

INSERT INTO data_tree (anc, des, depth)
  SELECT t.anc, :nodeValue0, t.depth+1
FROM data_tree AS t
  WHERE t.des = :insertedAt
UNION ALL
  SELECT :nodeValue1, :nodeValue2, 0

I have thought about some different solution, but cant see the solution. One idea is to 'calculate' the node order for a given depth in a sql trigger on each insert. Another solution is to update the above statement to support directly update of the depth order, such as:

INSERT INTO data_tree (anc, des, depth)
  SELECT t.anc, :nodeValue0, t.depth+1, --> t1.depthOrder <--
FROM data_tree AS t
  --> JOIN data_tree t1 'where the node order can be determined from' <--
  WHERE t.des = :insertedAt
UNION ALL
  SELECT :nodeValue1, :nodeValue2, 0, 0

Have anybody tried this, and how can it be solved?

BenMorel
  • 34,448
  • 50
  • 182
  • 322
user3747300
  • 21
  • 2
  • Can some one please close this, as the answer can be found here: [link](http://stackoverflow.com/questions/14069674/sorting-a-subtree-in-a-closure-table-hierarchical-data-structure/14074310#14074310). Bill Karwin saves the day again :) – user3747300 Jun 19 '14 at 22:41
  • Possible duplicate of [Sorting a subtree in a closure table hierarchical-data structure](http://stackoverflow.com/questions/14069674/sorting-a-subtree-in-a-closure-table-hierarchical-data-structure) – philipxy Mar 26 '17 at 23:21

0 Answers0