Find parent nodes in time trees by SQL

1.Find all ancestor node from child.

SELECT parent.name from time_trees as parent,time_trees as node where node.lft between parent.lft and parent.rgt and node.original_id='0036994d34de9862-03-00000000000324'


2.Find all children from parent.

select node.name from time_trees as node,time_trees as parent where node.lft between parent.lft and parent.rgt and parent.name='projects' order by node.lft


3.Insert node.

SELECT @myRight := rgt FROM time_trees
WHERE name = 'Projects';

UPDATE time_trees SET rgt = rgt + 2 WHERE rgt > @myRight;
UPDATE time_trees SET lft = lft + 2 WHERE lft > @myRight;

INSERT INTO time_trees(name, lft, rgt) VALUES('new record', @myRight + 1, @myRight + 2);