title: Depth in MYSQL and Closure Table Trees
body: <p>How would I go about populating a closure table's depth/length column when inserting a new node to the tree?</p>
  
  <p>The values in ancestor and descendant are IDs from another table that represent pages to be arranged in a tree structure.</p>
  
  <p>Closure Table:</p>
  
  <pre><code>ancestor    descendant     depth
  1               1            0
  1               2            1
  1               3            1 
  1               4            1
  2               2            0
  3               3            0 
  4               4            0
  </code></pre>
  
  <p>This will insert the ancestor and descendants properly but I'm not sure how to populate the depth column
  Insert Query:</p>
  
  <pre><code>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}';
  </code></pre>
  
  <p>What's the best way to go about this? Thanks a bunch!</p>