Глубина в MYSQL и деревьях таблиц закрытия

Question

Как бы я заполнил столбец глубины / длины таблицы закрытия при вставке нового узла в дерево?

Значения в предке и потомке - это идентификаторы из другой таблицы, которые представляют страницы, которые должны быть упорядочены в древовидной структуре.

Закрытие таблицы:

ancestor    descendant     depth
1               1            0
1               2            1
1               3            1 
1               4            1
2               2            0
3               3            0 
4               4            0

Это вставит предка и потомков правильно, но я не уверен, как заполнить столбец глубины Вставить запрос:

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}';

Какой лучший способ пойти по этому поводу? Огромное спасибо!

Answer 1

Добавьте глубину +1 к первому 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

Эта ветка мне очень помогла, но касалась только новых вставок, а не перемещений поддерева. Поэтому я подумал, что добавлю, что если вы используете подход перекрестного соединения для перемещения поддеревьев на разные узлы-предки, как описано в книге SQL Antipatterns , и вам нужно вычислить глубину, вы захотите сделать это следующим образом:

(Предполагается, что вы уже выполнили запрос на удаление для удаления путей предыдущих предков для этого поддерева, которое будет перемещено.)

      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>;

Это должно сохранить как глубину поддерева (которая не меняется), так и правильно пересчитать правильную глубину всех новых путей предков.