Skip to content

Instantly share code, notes, and snippets.

@emmanuel
Created June 2, 2011 07:54
  • Star 27 You must be signed in to star a gist
  • Fork 10 You must be signed in to fork a gist
Star You must be signed in to star a gist
Save emmanuel/1004087 to your computer and use it in GitHub Desktop.
Closure Table operations SQL fragments
-- 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;
-- 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;
@kouddy
Copy link

kouddy commented Jan 6, 2016

@emmanuel For inserting a sub-tree with path_length, you could calculate path_length by using

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

@kentoj
Copy link

kentoj commented Dec 16, 2017

See my updated fork here: https://gist.github.com/kentoj/872cbefc68f68a2a97b6189da9cd6e23
I am using all this code with slight modifications in a production application and have test proving it works.

@chibyk101
Copy link

chibyk101 commented Apr 18, 2021

how is the initial root node inserted?
lets say the very 1st comment which doesn't have a parent comment or ancestor,

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment