Created
June 2, 2011 07:54
-
-
Save emmanuel/1004087 to your computer and use it in GitHub Desktop.
Closure Table operations SQL fragments
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- 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; |
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
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.