By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 3799194 fiddles created (41759 in the last week).
CREATE TABLE gear_category (
id int PRIMARY KEY
, name text
, parent_id int
);
INSERT INTO gear_category VALUES
(1, 'foo' , NULL)
, (2, 'bar' , 1)
, (3, 'bar1', 2) -- 0 items
, (4, 'bar2', 2) -- 2 items
, (5, 'bar2', 3)
, (6, 'bar2', 3)
, (7, 'bar2', 3) -- 3 items
;
CREATE TABLE gear_item (
id int
, name text
, category_id int REFERENCES gear_category
);
INSERT INTO gear_item VALUES
(1, 'cat1', 1)
, (2, 'cat2', 2)
, (3, 'cat3', 4)
, (4, 'cat4', 4)
, (5, 'cat4', 7)
, (6, 'cat4', 7)
, (7, 'cat4', 7)
;
✓
7 rows affected
✓
7 rows affected
hidden batch(es)
CREATE OR REPLACE FUNCTION f_tree_ct()
RETURNS TABLE (id int, count_direct_child_items int, count_recursive_child_items int)
LANGUAGE plpgsql AS
$func$
DECLARE
_lvl int;
BEGIN
-- basic table with added path and count
CREATE TEMP TABLE t1 AS
WITH RECURSIVE tree AS (
SELECT c.id, c.parent_id, '{}'::int[] as path, 0 AS lvl
FROM gear_category c
WHERE c.parent_id IS NULL
UNION ALL
SELECT c.id, c.parent_id, path || c.parent_id, lvl + 1
FROM tree t
JOIN gear_category c ON c.parent_id = t.id
)
, tree_ct AS (
SELECT t.id, t.parent_id, t.path, t.lvl, COALESCE(i.item_ct, 0) AS item_ct
FROM tree t
LEFT JOIN (
SELECT i.category_id AS id, count(*)::int AS item_ct
FROM gear_item i
GROUP BY 1
) i USING (id)
)
TABLE tree_ct;
-- CREATE INDEX ON t1 (lvl); -- only for very deep trees
SELECT INTO _lvl max(lvl) FROM t1; -- identify max lvl to start bottom up
-- recursively aggregate each level in 2nd temp table
CREATE TEMP TABLE t2 AS
SELECT t1.id, t1.parent_id, t1.lvl
, t1.item_ct
, t1.item_ct AS sum_ct
FROM t1
WHERE t1.lvl = _lvl;
IF _lvl > 0 THEN
FOR i IN REVERSE _lvl .. 1 LOOP
INSERT INTO t2
SELECT t1.id, t1.parent_id, t1.lvl, t1.item_ct
, CASE WHEN t2.sum_ct IS NULL THEN t1.item_ct ELSE t1.item_ct + t2.sum_ct END
FROM t1
LEFT JOIN (
SELECT t2.parent_id AS id, sum(t2.sum_ct) AS sum_ct
FROM t2
WHERE t2.lvl = i
GROUP BY 1
) t2 USING (id)
WHERE t1.lvl = i - 1;
END LOOP;
END IF;
RETURN QUERY -- only requested columns, unsorted
SELECT t2.id, t2.item_ct, t2.sum_ct FROM t2;
DROP TABLE t1, t2; -- to allow repeated execution in one transaction
RETURN;
END
$func$;
✓
hidden batch(es)
SELECT * FROM f_tree_ct() ORDER BY id;
id
count_direct_child_items
count_recursive_child_items
1
1
7
2
1
6
3
0
3
4
2
2
5
0
0
6
0
0
7
3
3
…
hidden batch(es)
WITH RECURSIVE tree AS (
SELECT id, parent_id, ARRAY[id] as path
FROM gear_category
WHERE parent_id IS NULL
UNION ALL
SELECT c.id, c.parent_id, path || c.id
FROM tree t
JOIN gear_category c ON c.parent_id = t.id
)
, tree_ct AS (
SELECT t.id, t.path, COALESCE(i.item_ct, 0) AS item_ct
FROM tree t
LEFT JOIN (
SELECT category_id AS id, count(*) AS item_ct
FROM gear_item
GROUP BY 1
) i USING (id)
)
SELECT t.id
, t.item_ct AS count_direct_child_items
, sum(t1.item_ct) AS count_recursive_child_items
FROM tree_ct t
LEFT JOIN tree_ct t1 ON t1.path[1:array_upper(t.path, 1)] = t.path
GROUP BY t.id, t.item_ct
ORDER BY t.id;
