Skip to content

Instantly share code, notes, and snippets.

@coryodaniel
Forked from emmanuel/file.sql
Created October 17, 2016 01:06
Show Gist options
  • Save coryodaniel/d8d80283f1d44a6be0537c6b539a5690 to your computer and use it in GitHub Desktop.
Save coryodaniel/d8d80283f1d44a6be0537c6b539a5690 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;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment