Adding a sequence of cells chained by id reference in postgresql

Question

I have a table foo with columns id, number_of_foos, and parent_foo. number_of_foos is an integer and parent_foo is a reference to another row's id. Each row will have an integer in parent_foo or null if it has no parent.

id | number_of_foos | parent_foo
---+----------------+-----------
 1 |             10 |       null
 2 |             7  |       null
 3 |             6  |       null
 4 |             13 |          1
 5 |             9  |          3
 6 |             1  |          4

Given an id number, I want to find the total amount of "foo"s in the "foo chain", i.e. the count of foos for that id and its parent (and its parent, and its parent...). So for example, the total amount of foos WHEN id = 6 is 1 + 13 + 10 = 24.

Okay.

BUT, I also want to subtract 1 for each parent. So WHEN id = 6 is actually (1 + 13 + 10) - 2 = 22.

Is this possible?

S-Man · Accepted Answer · 2018-10-30T12:19:18.003

demo: db<>fiddle

WITH RECURSIVE rec AS (
    SELECT 
        parent_foo,
        number_of_foos
    FROM foo
    WHERE id = 6

    UNION

    SELECT
        f.parent_foo,
        r.number_of_foos + f.number_of_foos - 1
    FROM foo f
    JOIN rec r ON f.id = r.parent_foo
)
SELECT number_of_foos 
FROM rec
WHERE parent_foo IS NULL;

Using a WITH RECURSIVE CTE you could run through your data structure. A recursive CTE consists of two parts:

Starting point: Selecting the first row. In your case the parent of the first value and its number_of_foos value.
The recursion part where you select the row with id == parent_foo of last recursion. In this step you can integrate the adding part as well: Just adding the new numbers_of_foo - 1 to the last one.

Finally you can give out the row without any parent which is the grandmost parent of your starting value and it contains the expected sum.

Adding a sequence of cells chained by id reference in postgresql

1 Answers1