In postgresql using query
WITH RECURSIVE rel_rec AS (
SELECT
1 AS depth,
*,
ARRAY[lvl] AS child_path
FROM types_of_work
WHERE parent_id IS NULL AND project_id = 14
UNION ALL
SELECT
nlevel(r.path) + 1,
n.*,
r.child_path || n.lvl
FROM rel_rec AS r
JOIN types_of_work AS n ON n.parent_id = r.tow_id
WHERE r.project_id = 14
)
SELECT
t0.tow_id,
t0.project_id,
t0.path,
t0.child_path,
t0.lvl,
t0.parent_id,
t0.depth,
t2.tow_cost
FROM rel_rec t0
LEFT JOIN (
SELECT
tow_id,
tow_cost,
tow_cost_percent,
tow_date_start,
tow_date_finish
FROM tows_contract
WHERE contract_id = 10
) AS t2 ON t0.tow_id = t2.tow_id
ORDER BY t0.child_path, t0.lvl;
we get a table
tow_id integer | path ltree | child_path smallint[] | lvl smallint | parent_id integer | depth integer | cost numeric | CUMULATIVE_COST |
---|---|---|---|---|---|---|---|
39 | root | {1} | 1 | null | 1 | ∑ 90 | |
40 | root.39 | {1,2} | 2 | 39 | 2 | 10 | |
131 | root.39 | {1,3} | 3 | 39 | 2 | 10 | |
41 | root.39 | {1,4} | 4 | 39 | 2 | 10 | |
46 | root.39 | {1,5} | 5 | 39 | 2 | ∑ 60 | |
47 | root.39.46 | {1,5,6} | 6 | 46 | 3 | ∑ 30 | |
48 | root.39.46.47 | {1,5,6,7} | 7 | 47 | 4 | ∑ 10 | |
134 | root.39.46.47.48 | {1,5,6,7,8} | 8 | 48 | 5 | 10 | |
49 | root.39.46.47 | {1,5,6,9} | 9 | 47 | 4 | 10 | |
125 | root.39.46.47 | {1,5,6,10} | 10 | 47 | 4 | 10 | |
132 | root.39.46 | {1,5,11} | 11 | 46 | 3 | ∑ 20 | |
133 | root.39.46.132 | {1,5,11,12} | 12 | 132 | 4 | 10 | |
135 | root.39.46.132 | {1,5,11,13} | 13 | 132 | 4 | 10 | |
136 | root.39.46 | {1,5,14} | 14 | 46 | 3 | ∑ 10 | |
657 | root.39.46.136 | {1,5,14,15} | 15 | 136 | 4 | 10 | |
142 | root | {16} | 16 | null | 1 | ∑ 30 | |
143 | root.142 | {16,17} | 17 | 142 | 2 | ∑ 30 | |
178 | root.142.143 | {16,17,18} | 18 | 143 | 3 | 10 | |
146 | root.142.143 | {16,17,19} | 19 | 143 | 3 | 10 | |
147 | root.142.143 | {16,17,20} | 20 | 143 | 3 | 10 | |
42 | root | {21} | 21 | null | 1 | ∑ 10 | |
43 | root.39.42 | {21,22} | 22 | 42 | 2 | ∑ 10 | |
45 | root.39.42.43 | {21,22,23} | 23 | 43 | 3 | ∑ 10 | |
671 | root.39.42.43.45 | {21,22,23,24} | 24 | 45 | 4 | ∑ 10 | |
672 | root.39.42.43.45.671 | {21,22,23,24,25} | 25 | 671 | 5 | 10 |
How to add the calculated sum of the costs of all nested children to this table? An example of this sum is the CUMULATIVE_COST
column.
The table presented is a tree system.
The depth
column shows the nesting level
The parent_id
column shows the tow_id
of the parent entity