Aggregating nested set models with field constraints

Question

I have a table built on the nested set model that basically records transactions of different natures (defined by the field idx):

pk lft rgt idx value
 1 1 30 2783 20402.710000
 2 2 3 2783 -13885.670000
 3 4 5 131 78.547946
 4 6 7 2783 -586.810000
 5 8 9 1 0.013421
 6 10 11 2783 -1777.630000
 7 12 13 873 10791.237266
 8 14 15 2783 -72.510000
 9 16 17 1697 128.626280
10 18 19 2783 -600.430000
11 20 21 1168 6.949175
12 22 23 2783 -2479.080000
13 24 25 1758 853.502787
14 26 27 2783 -1000.580000
15 28 29 3794 3172.428304
16 31 40 2783 615.940000
17 32 33 2783 -615.940000
18 34 39 1 0.040390
19 35 36 1 -0.000152
20 37 38 1 -0.000300
21 41 42 2783 3012.050000

the three represented in the nested set

Since these transactions are nested in a particular way where a child node can be of different nature than its parent (representing a sort of "transformation" along the way), I am having a hard time trying to fetch the "balance" for each parent node in their own nature:

prnt_pk idx aggr
1 2783 0.000000
3 131 78.547946
5 1 0.013421
7 873 10791.237266
9 1697 128.626280
11 1168 6.949175
13 1758 853.502787
15 3794 3172.428304
16 2783 0.000000
18 1 0.039938
21 2783 3012.050000

How could I achieve this desired output given the original table? I have tried doing something along the lines described in this question, but all I have managed to get was a single parent node and all its children with the same idx, which I could do more easily in my case with SELECT * FROM table WHERE idx = 2783 or similar.

In case it helps, every parent node has a positive value, no node with negative values are allowed to have children.

Answer 1

These are the steps to obtain the desired result:

1. A cross self-join to obtain every single combination of primary keys possible (this squares the number of records);
2. Apply the nested set filter (commented below) to remove all combinations of nodes that are not related (e.g., pk 1 and 15);
3. Assert that only combinations of the same nature are being fetched and aggregated, by equalling the idx from both node and crosst;
4. Since we are aggregating from crosst.idx, that means it will sum the "balance" for all nodes, including those that are negative (and thus orphans); the third filter ensures we are only taking the total balance starting from original positive parents.

SELECT
 crosst.pk AS prnt_pk,
 crosst.idx,
 SUM(node.value) AS aggr
FROM
 nsm_table AS node,
 nsm_table AS crosst
WHERE 1=1
 AND node.lft BETWEEN crosst.lft AND crosst.rgt --NSM filter for containers
 AND node.idx = crosst.idx
 AND crosst.value > 0
GROUP BY crosst.pk, crosst.idx
HAVING SUM(node.value) >= 0

And the output is exactly as the desired one:

prnt_pk idx aggr
1 2783 0.000000
3 131 78.547946
5 1 0.013421
7 873 10791.237266
9 1697 128.626280
11 1168 6.949175
13 1758 853.502787
15 3794 3172.428304
16 2783 0.000000
18 1 0.039938
21 2783 3012.050000