Closure table examples

file.sql

-- Retrieve descendants
-- ====================
-- retrieve descendants of #4

SELECT c.*
FROM Comments AS c
  JOIN TreePaths AS t ON c.comment_id = t.descendant
WHERE t.ancestor = 4;

-- Retrieve ancestors
-- ==================
-- retrieve ancestors of #6

SELECT c.*
FROM Comments AS c
  JOIN TreePaths AS t ON c.comment_id = t.ancestor
WHERE t.descendant = 6;

-- Insert Leaf node
-- ================
-- insert leaf node #8 as a child of #5

INSERT INTO TreePaths (ancestor, descendant)
  SELECT t.ancestor, 8
  FROM TreePaths AS t
  WHERE t.descendant = 5
UNION ALL
  SELECT 8, 8;

-- with depth column

INSERT INTO TreePaths (ancestor, descendant, depth)
SELECT 8, 8, 0 
UNION ALL
SELECT ancestor, 8, depth+1
FROM TreePaths

WHERE descendant = 5;

-- I believe this is the same as above, with path_length
-- INSERT INTO TreePaths (ancestor, descendant, path_length)
--   SELECT t.ancestor, 8, t.path_length + 1
--   FROM TreePaths AS t
--   WHERE t.descendant = 5
-- UNION ALL
--   SELECT 8, 8, 0;

-- Delete Leaf node
-- ================
-- delete leaf node #7

DELETE FROM TreePaths WHERE descendant = 7;

-- Delete Subtree
-- ==============
-- delete #4 and all children from the tree

DELETE FROM TreePaths
WHERE descendant IN (SELECT descendant
                     FROM TreePaths
                     WHERE ancestor = 4);

-- Move Subtree
-- ============
--
-- reparent #6 from #4 -> #3
--
-- Step 1: Disconnect from current ancestors
-- -----------------------------------------
--
-- delete all paths that end at descendants in the subtree
-- or that begin with

DELETE FROM TreePaths
WHERE descendant IN (SELECT descendant
                     FROM TreePaths
                     WHERE ancestor = 6)
    AND ancestor IN (SELECT ancestor
                     FROM TreePaths
                     WHERE descendant = 6
                     AND ancestor != descendant);

-- Step 2: Insert rows matching ancestors of insertion point and descendants of subtree
-- ------------------------------------------------------------------------------------
-- TODO: how to calculate the path length of the new row in this query?

INSERT INTO TreePaths (ancestor, descendant)
  SELECT supertree.ancestor, subtree.descendant
  FROM TreePaths AS supertree
  CROSS JOIN TreePaths AS subtree
  WHERE supertree.descendant = 3
  AND subtree.ancestor = 6;