DROP TABLE IF EXISTS categories CASCADE;
DROP TABLE IF EXISTS categories_closure CASCADE;
CREATE TABLE categories (
id serial PRIMARY KEY,
title VARCHAR (50) UNIQUE ,
parent_id INT,
FOREIGN KEY (parent_id) REFERENCES categories (id));
CREATE TABLE categories_closure (
ancestor_id INTEGER REFERENCES categories(id),
descendant_id INTEGER REFERENCES categories(id),
depth INTEGER NOT NULL,
PRIMARY KEY (ancestor_id, descendant_id));
INSERT INTO categories(title, parent_id) VALUES('Electronics', NULL);
INSERT INTO categories(title, parent_id) VALUES('Televisions', 1);
INSERT INTO categories(title, parent_id) VALUES('Portables', 1);
INSERT INTO categories(title, parent_id) VALUES('Plasma', 2);
INSERT INTO categories(title, parent_id) VALUES('LCD', 2);
INSERT INTO categories(title, parent_id) VALUES('MP3', 3);
INSERT INTO categories(title, parent_id) VALUES('CD', 3);
INSERT INTO categories(title, parent_id) VALUES('Laptops', 3);
INSERT INTO categories(title, parent_id) VALUES('Osio', 6);
-- Having the categories table, the closure table can be populated with a recursive quety
INSERT INTO categories_closure (ancestor_id, descendant_id, depth)
WITH RECURSIVE category_tree AS (
-- Base case: self-referencing
SELECT id AS ancestor_id, id AS descendant_id, 0 AS depth
FROM categories
UNION ALL
-- Recursive step: find parents
SELECT c.parent_id, ct.descendant_id, ct.depth + 1
FROM categories c
JOIN category_tree ct ON c.id = ct.ancestor_id
WHERE c.parent_id IS NOT NULL
)
SELECT ancestor_id, descendant_id, depth FROM category_tree;
-- Alternatively, the closure table can be populated mannualy
-- For the Electronics node (Root)
INSERT INTO categories_closure (ancestor_id, descendant_id, depth) VALUES (1, 1, 0);
-- For the Televisions node (Parent: 1)
INSERT INTO categories_closure (ancestor_id, descendant_id, depth) VALUES (2, 2, 0);
INSERT INTO categories_closure (ancestor_id, descendant_id, depth) VALUES (1, 2, 1);
-- For the Portables node (Parent: 1)
INSERT INTO categories_closure (ancestor_id, descendant_id, depth) VALUES (3, 3, 0);
INSERT INTO categories_closure (ancestor_id, descendant_id, depth) VALUES (1, 3, 1);
-- For the Plasma node (Parent: 2)
INSERT INTO categories_closure (ancestor_id, descendant_id, depth) VALUES (4, 4, 0);
INSERT INTO categories_closure (ancestor_id, descendant_id, depth) VALUES (2, 4, 1);
INSERT INTO categories_closure (ancestor_id, descendant_id, depth) VALUES (1, 4, 2);
-- For the LCD node (Parent: 2)
INSERT INTO categories_closure (ancestor_id, descendant_id, depth) VALUES (5, 5, 0);
INSERT INTO categories_closure (ancestor_id, descendant_id, depth) VALUES (2, 5, 1);
INSERT INTO categories_closure (ancestor_id, descendant_id, depth) VALUES (1, 5, 2);
-- For the MP3 node (Parent: 3)
INSERT INTO categories_closure (ancestor_id, descendant_id, depth) VALUES (6, 6, 0);
INSERT INTO categories_closure (ancestor_id, descendant_id, depth) VALUES (3, 6, 1);
INSERT INTO categories_closure (ancestor_id, descendant_id, depth) VALUES (1, 6, 2);
-- For the CD node (Parent: 3)
INSERT INTO categories_closure (ancestor_id, descendant_id, depth) VALUES (7, 7, 0);
INSERT INTO categories_closure (ancestor_id, descendant_id, depth) VALUES (3, 7, 1);
INSERT INTO categories_closure (ancestor_id, descendant_id, depth) VALUES (1, 7, 2);
-- For the Laptops node (Parent: 3)
INSERT INTO categories_closure (ancestor_id, descendant_id, depth) VALUES (8, 8, 0);
INSERT INTO categories_closure (ancestor_id, descendant_id, depth) VALUES (3, 8, 1);
INSERT INTO categories_closure (ancestor_id, descendant_id, depth) VALUES (1, 8, 2);
-- For the Osio node (Parent: 6)
INSERT INTO categories_closure (ancestor_id, descendant_id, depth) VALUES (9, 9, 0);
INSERT INTO categories_closure (ancestor_id, descendant_id, depth) VALUES (6, 9, 1);
INSERT INTO categories_closure (ancestor_id, descendant_id, depth) VALUES (3, 9, 2);
INSERT INTO categories_closure (ancestor_id, descendant_id, depth) VALUES (1, 9, 3);
select * from categories;
select * from categories_closure;
/*
The script below is incomplete - it creates a closure table with only 17 entries
-- 1) Insert the root and its self-row
WITH ins AS (
INSERT INTO categories (parent_id, title) VALUES (NULL, 'Electronics')
RETURNING id
)
INSERT INTO categories_closure (ancestor_id, descendant_id, depth)
SELECT id, id, 0 FROM ins;
-- 2) Add child Televisions
WITH child AS (
INSERT INTO categories (parent_id, title) VALUES (1, 'Televisions')
RETURNING id
)
-- Insert self-row
INSERT INTO categories_closure (ancestor_id, descendant_id, depth)
SELECT id, id, 0 FROM child;
-- Link child to all ancestors of the parent
INSERT INTO categories_closure (ancestor_id, descendant_id, depth)
SELECT p.ancestor_id, c.id, p.depth + 1
FROM categories_closure AS p
CROSS JOIN (SELECT id FROM categories WHERE title = 'Electronics') AS c
WHERE p.descendant_id = 2;
-- 3) Add child Portables
WITH child AS (
INSERT INTO categories (parent_id, title) VALUES (1, 'Portables')
RETURNING id
)
-- Insert self-row
INSERT INTO categories_closure (ancestor_id, descendant_id, depth)
SELECT id, id, 0 FROM child;
-- Link child to all ancestors of the parent
INSERT INTO categories_closure (ancestor_id, descendant_id, depth)
SELECT p.ancestor_id, c.id, p.depth + 1
FROM categories_closure AS p
CROSS JOIN (SELECT id FROM categories WHERE title = 'Electronics') AS c
WHERE p.descendant_id = 3;
-- 4) Add child PLASMA with parent Televisions
WITH child AS (
INSERT INTO categories (parent_id, title) VALUES (2, 'Plasma')
RETURNING id
)
-- Insert self-row
INSERT INTO categories_closure (ancestor_id, descendant_id, depth)
SELECT id, id, 0 FROM child;
-- Link child to all ancestors of the parent
INSERT INTO categories_closure (ancestor_id, descendant_id, depth)
SELECT p.ancestor_id, c.id, p.depth + 1
FROM categories_closure AS p
CROSS JOIN (SELECT id FROM categories WHERE title = 'Televisions') AS c
WHERE p.descendant_id = 4;
-- 5) Add child LCD with parent Televisions
WITH child AS (
INSERT INTO categories (parent_id, title) VALUES (2, 'LCD')
RETURNING id
)
-- Insert self-row
INSERT INTO categories_closure (ancestor_id, descendant_id, depth)
SELECT id, id, 0 FROM child;
-- Link child to all ancestors of the parent
INSERT INTO categories_closure (ancestor_id, descendant_id, depth)
SELECT p.ancestor_id, c.id, p.depth + 1
FROM categories_closure AS p
CROSS JOIN (SELECT id FROM categories WHERE title = 'Televisions') AS c
WHERE p.descendant_id = 5;
-- 6) Add child MP3 with parent Portables
WITH child AS (
INSERT INTO categories (parent_id, title) VALUES (3, 'MP3')
RETURNING id
)
-- Insert self-row
INSERT INTO categories_closure (ancestor_id, descendant_id, depth)
SELECT id, id, 0 FROM child;
-- Link child to all ancestors of the parent
INSERT INTO categories_closure (ancestor_id, descendant_id, depth)
SELECT p.ancestor_id, c.id, p.depth + 1
FROM categories_closure AS p
CROSS JOIN (SELECT id FROM categories WHERE title = 'Portables') AS c
WHERE p.descendant_id = 6;
-- 7) Add child CD with parent Portables
WITH child AS (
INSERT INTO categories (parent_id, title) VALUES (3, 'CD')
RETURNING id
)
-- Insert self-row
INSERT INTO categories_closure (ancestor_id, descendant_id, depth)
SELECT id, id, 0 FROM child;
-- Link child to all ancestors of the parent
INSERT INTO categories_closure (ancestor_id, descendant_id, depth)
SELECT p.ancestor_id, c.id, p.depth + 1
FROM categories_closure AS p
CROSS JOIN (SELECT id FROM categories WHERE title = 'Portables') AS c
WHERE p.descendant_id = 7;
-- 8) Add child Laptops with parent Portables
WITH child AS (
INSERT INTO categories (parent_id, title) VALUES (3, 'Laptops')
RETURNING id
)
-- Insert self-row
INSERT INTO categories_closure (ancestor_id, descendant_id, depth)
SELECT id, id, 0 FROM child;
-- Link child to all ancestors of the parent
INSERT INTO categories_closure (ancestor_id, descendant_id, depth)
SELECT p.ancestor_id, c.id, p.depth + 1
FROM categories_closure AS p
CROSS JOIN (SELECT id FROM categories WHERE title = 'Portables') AS c
WHERE p.descendant_id = 8;
-- 9) Add child Osio with parent Portables
WITH child AS (
INSERT INTO categories (parent_id, title) VALUES (6, 'Osio')
RETURNING id
)
-- Insert self-row
INSERT INTO categories_closure (ancestor_id, descendant_id, depth)
SELECT id, id, 0 FROM child;
-- Link child to all ancestors of the parent
INSERT INTO categories_closure (ancestor_id, descendant_id, depth)
SELECT p.ancestor_id, c.id, p.depth + 1
FROM categories_closure AS p
CROSS JOIN (SELECT id FROM categories WHERE title = 'MP3') AS c
WHERE p.descendant_id = 9;
*/
-- Finding the immediate children (depth = 1) of node with id=3
SELECT n.*
FROM categories_closure nc
JOIN categories n ON n.id = nc.descendant_id
WHERE nc.ancestor_id = 3
AND nc.depth = 1 ;
-- Finding all children (depth > 0) of node with id=3
SELECT n.*
FROM categories_closure nc
JOIN categories n ON n.id = nc.descendant_id
WHERE nc.ancestor_id = 3
AND nc.depth > 0 ;
-- Finding all ancestors of node with id=9 (πρόγονοι του Osio) - asc order
SELECT a.*, nc.depth
FROM categories_closure nc
JOIN categories a ON a.id = nc.ancestor_id
WHERE nc.descendant_id = 9 AND nc.depth > 0
ORDER BY nc.depth ASC; -- root first
-- Finding all ancestors of node with id=9 (πρόγονοι του Osio) - desc order
SELECT c.title, cp.ancestor_id, cp.depth
FROM categories c
JOIN categories_closure cp ON c.id = cp.ancestor_id
WHERE cp.descendant_id = 9 AND cp.depth > 0
ORDER BY cp.depth DESC;
-- All descendants of root node (full subtree)
SELECT d.*, nc.depth
FROM categories_closure nc
JOIN categories d ON d.id = nc.descendant_id
WHERE nc.ancestor_id = 1 AND nc.depth > 0
ORDER BY nc.depth ASC; -- closest children first
-- Find all descendants of a node (ID 3)
SELECT c.* FROM categories c
JOIN categories_closure cc ON c.id = cc.descendant_id
WHERE cc.ancestor_id = 3 AND cc.depth > 0;
-- Find the path to the parent (Ancestors/ Απόγονοι):
SELECT c.* FROM categories c
JOIN categories_closure cc ON c.id = cc.ancestor_id
WHERE cc.descendant_id = 2
ORDER BY cc.depth DESC;
select * from categories;
select * from categories_closure;
-- Finding the root node
SELECT *
FROM categories c
WHERE parent_id IS NULL;
-- Finding the immediate children of a node
SELECT c.id, c.title
FROM categories c
WHERE parent_id = 6;
-- Finding how many immediate children each node has
SELECT parents.title, COUNT(c2.id)
FROM categories parents
JOIN categories c2 ON c2.parent_id = parents.id
GROUP BY parents.title;
-- Finding the leaf nodes
SELECT c.id, c.title
FROM categories c
WHERE c.id NOT IN (
SELECT parent_id FROM categories c2
WHERE parent_id IS NOT NULL);
--Using recursion
-- Finding the parent of a node by id
WITH RECURSIVE categories_closure (id, title, parent_id) AS
(
SELECT id, title, parent_id
FROM categories
WHERE id = 9
UNION ALL
select c.id, c.title, cp.parent_id
FROM categories_closure AS cp JOIN categories AS c
ON cp.id = c.parent_id
)
Select * from categories_closure
LIMIT 1;
-- Finding the parent of a node by title
WITH RECURSIVE categories_closure (id, title, parent_id) AS
(
SELECT id, title, parent_id
FROM categories
WHERE title = 'Osio'
UNION ALL
select c.id, c.title, cp.parent_id
FROM categories_closure AS cp JOIN categories AS c
ON cp.id = c.parent_id
)
Select * from categories_closure
LIMIT 1;
-- Finding full path for each node
WITH RECURSIVE categories_closure (id, title, path) AS
(
SELECT id, title, title as path
FROM categories
WHERE parent_id IS NULL
UNION ALL
SELECT c.id, c.title, CONCAT(cp.path, ' --> ', c.title)::varchar(50)
FROM categories_closure AS cp JOIN categories AS c
ON cp.id = c.parent_id
)
SELECT * FROM categories_closure
ORDER BY path;
-- Finding full path with array and level
WITH RECURSIVE categories_closure (id, title, parent_id, PATH, lvl) AS
(
SELECT id, title, parent_id, string_to_array(title, '') as PATH, 0 AS lvl
FROM categories
WHERE parent_id IS NULL
UNION ALL
SELECT c.id, c.title, c.parent_id, array_cat(cp.PATH, string_to_array(c.title, '')), cp.lvl + 1 AS level
FROM categories_closure AS cp JOIN categories AS c ON cp.id = c.parent_id
)
SELECT * FROM categories_closure;
-- Querying a sub-tree
WITH RECURSIVE categories_closure (id, title, parent_id, PATH, lvl) AS
(
SELECT id, title, parent_id, string_to_array(title, '') as PATH, 0 AS lvl
FROM categories
WHERE id = 2
UNION ALL
SELECT c.id, c.title, c.parent_id, array_cat(cp.PATH, string_to_array(c.title, '')), cp.lvl + 1 AS level
FROM categories_closure AS cp JOIN categories AS c ON cp.id = c.parent_id
)
SELECT * FROM categories_closure;
-- Querying a single path from BOTTOM to TOP
WITH RECURSIVE categories_closure (id, title, parent_id, PATH, lvl) AS
(
SELECT id, title, parent_id, string_to_array(title, '') as PATH, 0 AS lvl
FROM categories
WHERE id = 2
--WHERE parent_id IS NULL
UNION ALL
SELECT c.id, c.title, c.parent_id, array_cat(cp.PATH, string_to_array(c.title, '')), cp.lvl + 1 AS level
FROM categories_closure AS cp JOIN categories AS c ON cp.id = c.parent_id
)
SELECT * FROM categories_closure;
/*
Inserting nodes in the tree
*/
-- Inserting a new root node
WITH parent AS (
INSERT INTO categories(parent_id)
VALUES (NULL)
RETURNING id
)
UPDATE categories
SET parent_id = parent.id
FROM parent
WHERE parent_id IS NULL;
-- Inserting a node between two existing nodes
WITH created_node AS (
INSERT INTO categories(parent_id)
VALUES (1)
RETURNING id
)
UPDATE categories
SET parent_id = created_node.id
FROM created_node
WHERE categories.id = 2;
-- Adding a child node, that gets it’s siblings as children
WITH created_node AS (
INSERT INTO categories(parent_id)
VALUES (2)
RETURNING id
)
UPDATE categories
SET parent_id = created_node.id
FROM created_node
WHERE categories.parent_id = 2;
SELECT *
FROM categories;