How would I go about populating a closure table's depth/length column when inserting a new node to the tree?
The values in ancestor and descendant are IDs from another table that represent pages to be arranged in a tree structure.
Closure Table:
ancestor descendant depth
1 1 0
1 2 1
1 3 1
1 4 1
2 2 0
3 3 0
4 4 0
This will insert the ancestor and descendants properly but I'm not sure how to populate the depth column Insert Query:
INSERT INTO closure_tree_path (ancestor, descendant)
SELECT ancestor, '{$node_id}' FROM closure_tree_path
WHERE descendant = '{$parent_id}'
UNION ALL SELECT '{$node_id}', '{$node_id}';
What's the best way to go about this? Thanks a bunch!
Add depth+1 to the first SELECT.
INSERT INTO closure_tree_path (ancestor, descendant, depth)
SELECT ancestor, '{$node_id}', depth+1 FROM closure_tree_path
WHERE descendant = '{$parent_id}'
UNION ALL SELECT '{$node_id}', '{$node_id}', 0;