Depth in MYSQL and Closure Table Trees

Question

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!

Answer 1

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;

Answer 2

This thread helped me immensely, but only addressed new inserts, not subtree moves. So I thought I'd add that if you use the cross join approach for moving subtrees to different ancestor nodes as described in the SQL Antipatterns book, and you need to calculate depth, you'll want to do it like this:

(This assumes you already executed the delete query for removing the prior ancestors' paths for this subtree about to be moved.)

INSERT INTO closure_tree_path (ancestor, descendant, depth)
  SELECT supertree.ancestor, subtree.descendant, supertree.depth + subtree.depth + 1
  FROM closure_tree_path AS supertree
    CROSS JOIN closure_tree_path AS subtree
  WHERE supertree.descendant = <new parent node ID>
    AND subtree.ancestor = <node ID to move>;

This should preserve both the depth of the subtree (which doesn't change), and will recalculate the proper depths of all new ancestor paths correctly.