MYSQL和关闭表树的深度 [英] Depth in MYSQL and Closure Table Trees

问题描述

在向树中插入新节点时，如何填充闭合表的depth/length列?

How would I go about populating a closure table's depth/length column when inserting a new node to the tree?

祖先和后代中的值是来自另一个表的ID，这些ID表示要以树形结构排列的页面.

The values in ancestor and descendant are IDs from another table that represent pages to be arranged in a tree structure.

关闭表:

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!

推荐答案

在第一个SELECT中添加depth + 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;

这篇关于MYSQL和关闭表树的深度的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！