Postgres Pro
Downloads
Home   >   mailing lists

Thread: Reporting by family tree

Reporting by family tree

From
Ibrahim Shaame
Date:

I have a table of members of a large family extendending back to eight generations. The current members contribute a monthly amount to the family fund. Only true descendants are included in the family list, no wives, no husbands. There are two tables

1 - Names with the following fields: idno (unique) --family member

parentid -- id number of the parent who connected the child to the family

etc

etc

2 – Contributions with fields: idno

etc

etc


Now I want to report Names and contributions par family tree: My ideal is to list grandfather, father, children based on the two fields (id, parentid).

Any suggestions?

Thanks in advance


Re: Reporting by family tree

From
swastik Gurung
Date:
I suppose you ought to be using, recursive CTE queries

Documentation can be found at: 7.8. WITH Queries (Common Table Expressions)




On Thursday, 5 October 2023 at 16:44:46 GMT+5:45, Ibrahim Shaame <ishaame@gmail.com> wrote:


I have a table of members of a large family extendending back to eight generations. The current members contribute a monthly amount to the family fund. Only true descendants are included in the family list, no wives, no husbands. There are two tables

1 - Names with the following fields: idno (unique) --family member

parentid -- id number of the parent who connected the child to the family

etc

etc

2 – Contributions with fields: idno

etc

etc


Now I want to report Names and contributions par family tree: My ideal is to list grandfather, father, children based on the two fields (id, parentid).

Any suggestions?

Thanks in advance


Re: Reporting by family tree

From
Ibrahim Shaame
Date:
Swastik, thank you for the response. I started there, and have been stuck for many months now. I managed to get only father-child did not get further. Here is the example sql where I get father-child results.
With this code:
WITH RECURSIVE ukoo AS (
    SELECT namba,
         jina,
         baba,
         babu,
         nasaba_1,
         daraja
    FROM majina2
    WHERE majina2.nasaba_1 IN (SELECT DISTINCT namba FROM majina2)
 
UNION ALL
 
    SELECT mtoto.namba,
         mtoto.jina,
         mtoto.baba,
         mtoto.babu,
         mtoto.nasaba_1,
         daraja
    FROM majina2 mtoto
          WHERE mtoto.nasaba_1 NOT IN (SELECT DISTINCT namba FROM majina2)
 
)
 
SELECT  g.jina AS jina_la_mtoto,
        g.baba AS baba_wa_mtoto,
        g.babu AS babu_wa_mtoto,
        g.namba,
        mzazi.jina AS jina_la_mzazi,
        mzazi.baba AS jina_la_baba_la_mzazi,
        g.daraja
FROM ukoo g
JOIN majina2 mzazi
ON g.namba = mzazi.namba
ORDER BY g.namba;

I get:

jina_la_mtoto baba_wa_mtoto babu_wa_mtoto namba jina_la_mzazi jina_la_baba_la_mzazi daraja
---------------+---------------+---------------+--------+---------------+-----------------------+--------





Ibrahim Khamis Haji 100001 Ibrahim Khamis 6
Asia Khamis Haji 100002 Asia Khamis 6
Zubeir Khamis Haji 100003 Zubeir Khamis 6
Asha Mwinyi Bakari 100004 Asha Mwinyi 6
Mariama Mwinyi Bakari 100005 Mariama Mwinyi 6
Zainab Ibrahim Khamis 100006 Zainab Ibrahim 7
Fatma Ibrahim Khamis 100007 Fatma Ibrahim 7







Shaban Ibrahim Khamis 100162 Shaban Ibrahim 7
Alicia Shaban Ibrahim 100163 Alicia Shaban 8

Ideally I should get

Ibrahim (father of Shaban)
then Shaban the father of Alicia)
Under Shaban should get Alicia
Then Zainab (sister of Shaban
Then Fatma (sister of Shaban)
Then another member (after I have got Ibrahim and his descendants)

Any idea?



On Thu, Oct 5, 2023 at 4:15 PM swastik Gurung <gurung_swastik@yahoo.com> wrote:
On Thursday, 5 October 2023 at 16:44:46 GMT+5:45, Ibrahim Shaame <ishaame@gmail.com> wrote:


I have a table of members of a large family extendending back to eight generations. The current members contribute a monthly amount to the family fund. Only true descendants are included in the family list, no wives, no husbands. There are two tables

1 - Names with the following fields: idno (unique) --family member

parentid -- id number of the parent who connected the child to the family

etc

etc

2 – Contributions with fields: idno

etc

etc


Now I want to report Names and contributions par family tree: My ideal is to list grandfather, father, children based on the two fields (id, parentid).

Any suggestions?

Thanks in advance


Re: Reporting by family tree

From
"David G. Johnston"
Date:
On Thu, Oct 5, 2023 at 7:54 AM Ibrahim Shaame <ishaame@gmail.com> wrote:
WITH RECURSIVE ukoo AS (
    SELECT namba,
         jina,
         baba,
         babu,
         nasaba_1,
         daraja
    FROM majina2
    WHERE majina2.nasaba_1 IN (SELECT DISTINCT namba FROM majina2)
 
UNION ALL
 
    SELECT mtoto.namba,
         mtoto.jina,
         mtoto.baba,
         mtoto.babu,
         mtoto.nasaba_1,
         daraja
    FROM majina2 mtoto
          WHERE mtoto.nasaba_1 NOT IN (SELECT DISTINCT namba FROM majina2)


The reason it is called a "recursive" CTE is that the subquery following the union all is recursive in nature - i.e., it should refer to itself.  You named the CTE ukoo but you never actually refer to ukoo in the recursive subquery.  Thus, you have not written a recursive query.

When you reference the recursive "table" in the subquery its contents contain the results of the previous iteration, that is what allows you to select a child record and then consider that record a parent when finding the next depth/layer of children.

David J.

Re: Reporting by family tree

From
Ibrahim Shaame
Date:
Thanks David for the reply. But I think you missed part of the code, which refers to ukoo:
)
 
SELECT  g.jina AS jina_la_mtoto,
        g.baba AS baba_wa_mtoto,
        g.babu AS babu_wa_mtoto,
        g.namba,
        mzazi.jina AS jina_la_mzazi,
        mzazi.baba AS jina_la_baba_la_mzazi,
        g.daraja
FROM ukoo g
JOIN majina2 mzazi
ON g.namba = mzazi.namba
ORDER BY g.namba;

Any suggestion?
Thanks



On Thu, Oct 5, 2023 at 6:03 PM David G. Johnston <david.g.johnston@gmail.com> wrote:
On Thu, Oct 5, 2023 at 7:54 AM Ibrahim Shaame <ishaame@gmail.com> wrote:
WITH RECURSIVE ukoo AS (
    SELECT namba,
         jina,
         baba,
         babu,
         nasaba_1,
         daraja
    FROM majina2
    WHERE majina2.nasaba_1 IN (SELECT DISTINCT namba FROM majina2)
 
UNION ALL
 
    SELECT mtoto.namba,
         mtoto.jina,
         mtoto.baba,
         mtoto.babu,
         mtoto.nasaba_1,
         daraja
    FROM majina2 mtoto
          WHERE mtoto.nasaba_1 NOT IN (SELECT DISTINCT namba FROM majina2)


The reason it is called a "recursive" CTE is that the subquery following the union all is recursive in nature - i.e., it should refer to itself.  You named the CTE ukoo but you never actually refer to ukoo in the recursive subquery.  Thus, you have not written a recursive query.

When you reference the recursive "table" in the subquery its contents contain the results of the previous iteration, that is what allows you to select a child record and then consider that record a parent when finding the next depth/layer of children.

David J.

Re: Reporting by family tree

From
"David G. Johnston"
Date:
On Monday, October 16, 2023, Ibrahim Shaame <ishaame@gmail.com> wrote:
Thanks David for the reply. But I think you missed part of the code, which refers to ukoo:
)
 
SELECT  g.jina AS jina_la_mtoto,
        g.baba AS baba_wa_mtoto,
        g.babu AS babu_wa_mtoto,
        g.namba,
        mzazi.jina AS jina_la_mzazi,
        mzazi.baba AS jina_la_baba_la_mzazi,
        g.daraja
FROM ukoo g
JOIN majina2 mzazi
ON g.namba = mzazi.namba
ORDER BY g.namba;

Any suggestion?

That part of the query is outside the CTE and thus doesn’t impact the CTE’s results.  The part of the query that needs to be self-referencing is the subquery inside the CTE under the Union All.

David J.
 

Re: Reporting by family tree

From
swastik Gurung
Date:
Example Below:

-- create a test family table
create table family as
(
select
1 as id,
null::integer as parent_id,
'Grandfather1' as name
union all
select
2 as id,
null::integer as parent_id,
'Grandfather2' as name
union all
select
3 as id,
1 as parent_id,
'Father1-1' as name
union all
select
4 as id,
1 as parent_id,
'Father1-2' as name
union all
select
5 as id,
2 as parent_id,
'Father2-1' as name
union all
select
6 as id,
3 as parent_id,
'Son1-1-1' as name
union all
select
7 as id,
4 as parent_id,
'Son1-2-1' as name
union all
select
8 as id,
5 as parent_id,
'Son2-1-1' as name);

-- create a test contribution table
create table contribution as
(
select
1 as contributor_id,
'2020-01-01' as date,
300.00 as contribution_amount
union all
select
1 as contributor_id,
'2020-02-01' as date,
255.00 as contribution_amount
union all
select
1 as contributor_id,
'2020-03-01' as date,
45.65 as contribution_amount
union all
select
2 as contributor_id,
'2020-05-01' as date,
22.55 as contribution_amount
union all
select
2 as contributor_id,
'2020-01-01' as date,
450.00 as contribution_amount
union all
select
3 as contributor_id,
'2020-02-01' as date,
200.00 as contribution_amount
union all
select
4 as contributor_id,
'2020-03-01' as date,
150.00 as contribution_amount
union all
select
4 as contributor_id,
'2020-04-01' as date,
60.45 as contribution_amount
union all
select
4 as contributor_id,
'2020-05-01' as date,
300.00 as contribution_amount
union all
select
5 as contributor_id,
'2020-06-01' as date,
1250.00 as contribution_amount
union all
select
6 as contributor_id,
'2020-01-01' as date,
66.50 as contribution_amount
union all
select
7 as contributor_id,
'2020-02-01' as date,
855.00 as contribution_amount
union all
select
8 as contributor_id,
'2020-02-01' as date,
25.00 as contribution_amount);

-- execute recursive query, all children inheriting contribution sum of parents
with recursive cte as
(
select
f.id,
f.parent_id,
f.name,
c.contribution_amount
from
family f
join contribution c on
f.id = c.contributor_id
union all
select
f.id,
f.parent_id,
f.name,
cte.contribution_amount
from
cte
join family f on
cte.id = f.parent_id)
select
id,
name,
sum(contribution_amount) as total_contribution
from
cte
group by
id,
name
order by
id;

-- execute recursive query, parents have sum of all contributions of its children
with recursive cte as
(
select
f.id,
f.parent_id,
f.name,
c.contribution_amount
from
family f
join contribution c on
f.id = c.contributor_id
union all
select
f.id,
f.parent_id,
f.name,
cte.contribution_amount
from
cte
join family f on
cte.parent_id = f.id)
select
id,
name,
sum(contribution_amount) as total_contribution
from
cte
group by
id,
name
order by
id;


Change your SQL accordingly. Also, you can add month field to yield results per month.

Re: Reporting by family tree

From
Ibrahim Shaame
Date:
Thank you David and Swastik, Swastik, I will work on it and will let you know.
Thanks again for your help

On Mon, Oct 16, 2023 at 4:31 PM swastik Gurung <gurung_swastik@yahoo.com> wrote:
Example Below:

-- create a test family table
create table family as
(
select
1 as id,
null::integer as parent_id,
'Grandfather1' as name
union all
select
2 as id,
null::integer as parent_id,
'Grandfather2' as name
union all
select
3 as id,
1 as parent_id,
'Father1-1' as name
union all
select
4 as id,
1 as parent_id,
'Father1-2' as name
union all
select
5 as id,
2 as parent_id,
'Father2-1' as name
union all
select
6 as id,
3 as parent_id,
'Son1-1-1' as name
union all
select
7 as id,
4 as parent_id,
'Son1-2-1' as name
union all
select
8 as id,
5 as parent_id,
'Son2-1-1' as name);

-- create a test contribution table
create table contribution as
(
select
1 as contributor_id,
'2020-01-01' as date,
300.00 as contribution_amount
union all
select
1 as contributor_id,
'2020-02-01' as date,
255.00 as contribution_amount
union all
select
1 as contributor_id,
'2020-03-01' as date,
45.65 as contribution_amount
union all
select
2 as contributor_id,
'2020-05-01' as date,
22.55 as contribution_amount
union all
select
2 as contributor_id,
'2020-01-01' as date,
450.00 as contribution_amount
union all
select
3 as contributor_id,
'2020-02-01' as date,
200.00 as contribution_amount
union all
select
4 as contributor_id,
'2020-03-01' as date,
150.00 as contribution_amount
union all
select
4 as contributor_id,
'2020-04-01' as date,
60.45 as contribution_amount
union all
select
4 as contributor_id,
'2020-05-01' as date,
300.00 as contribution_amount
union all
select
5 as contributor_id,
'2020-06-01' as date,
1250.00 as contribution_amount
union all
select
6 as contributor_id,
'2020-01-01' as date,
66.50 as contribution_amount
union all
select
7 as contributor_id,
'2020-02-01' as date,
855.00 as contribution_amount
union all
select
8 as contributor_id,
'2020-02-01' as date,
25.00 as contribution_amount);

-- execute recursive query, all children inheriting contribution sum of parents
with recursive cte as
(
select
f.parent_id,
c.contribution_amount
from
family f
join contribution c on
f.id = c.contributor_id
union all
select
f.parent_id,
cte.contribution_amount
from
cte
join family f on
cte.id = f.parent_id)
select
id,
name,
sum(contribution_amount) as total_contribution
from
cte
group by
id,
name
order by
id;

-- execute recursive query, parents have sum of all contributions of its children
with recursive cte as
(
select
f.parent_id,
c.contribution_amount
from
family f
join contribution c on
f.id = c.contributor_id
union all
select
f.parent_id,
cte.contribution_amount
from
cte
join family f on
cte.parent_id = f.id)
select
id,
name,
sum(contribution_amount) as total_contribution
from
cte
group by
id,
name
order by
id;


Change your SQL accordingly. Also, you can add month field to yield results per month.

Re: Reporting by family tree

From
Ibrahim Shaame
Date:
Swastik, I have done as you suggested. What I get is:

id | name | total_contribution

----+--------------+--------------------

1 | Grandfather1 | 600.65

2 | Grandfather2 | 472.55

3 | Father1-1 | 800.65

4 | Father1-2 | 1111.10

5 | Father2-1 | 1722.55

6 | Son1-1-1 | 867.15

7 | Son1-2-1 | 1966.10

8 | Son2-1-1 | 1747.55


But what I want to get is  grandfather - father - children:

1 - Grandfather1

3 - father1-1

6 - son1-1

7 – son1-2

4 - Father1-2

8 - son2-1

2 – Grandfather2

5 - Father2-1

etc


Any suggestion

Thanks





On Tue, Oct 17, 2023 at 11:18 AM Ibrahim Shaame <ishaame@gmail.com> wrote:
Thank you David and Swastik, Swastik, I will work on it and will let you know.
Thanks again for your help

On Mon, Oct 16, 2023 at 4:31 PM swastik Gurung <gurung_swastik@yahoo.com> wrote:
Example Below:

-- create a test family table
create table family as
(
select
1 as id,
null::integer as parent_id,
'Grandfather1' as name
union all
select
2 as id,
null::integer as parent_id,
'Grandfather2' as name
union all
select
3 as id,
1 as parent_id,
'Father1-1' as name
union all
select
4 as id,
1 as parent_id,
'Father1-2' as name
union all
select
5 as id,
2 as parent_id,
'Father2-1' as name
union all
select
6 as id,
3 as parent_id,
'Son1-1-1' as name
union all
select
7 as id,
4 as parent_id,
'Son1-2-1' as name
union all
select
8 as id,
5 as parent_id,
'Son2-1-1' as name);

-- create a test contribution table
create table contribution as
(
select
1 as contributor_id,
'2020-01-01' as date,
300.00 as contribution_amount
union all
select
1 as contributor_id,
'2020-02-01' as date,
255.00 as contribution_amount
union all
select
1 as contributor_id,
'2020-03-01' as date,
45.65 as contribution_amount
union all
select
2 as contributor_id,
'2020-05-01' as date,
22.55 as contribution_amount
union all
select
2 as contributor_id,
'2020-01-01' as date,
450.00 as contribution_amount
union all
select
3 as contributor_id,
'2020-02-01' as date,
200.00 as contribution_amount
union all
select
4 as contributor_id,
'2020-03-01' as date,
150.00 as contribution_amount
union all
select
4 as contributor_id,
'2020-04-01' as date,
60.45 as contribution_amount
union all
select
4 as contributor_id,
'2020-05-01' as date,
300.00 as contribution_amount
union all
select
5 as contributor_id,
'2020-06-01' as date,
1250.00 as contribution_amount
union all
select
6 as contributor_id,
'2020-01-01' as date,
66.50 as contribution_amount
union all
select
7 as contributor_id,
'2020-02-01' as date,
855.00 as contribution_amount
union all
select
8 as contributor_id,
'2020-02-01' as date,
25.00 as contribution_amount);

-- execute recursive query, all children inheriting contribution sum of parents
with recursive cte as
(
select
f.parent_id,
c.contribution_amount
from
family f
join contribution c on
f.id = c.contributor_id
union all
select
f.parent_id,
cte.contribution_amount
from
cte
join family f on
cte.id = f.parent_id)
select
id,
name,
sum(contribution_amount) as total_contribution
from
cte
group by
id,
name
order by
id;

-- execute recursive query, parents have sum of all contributions of its children
with recursive cte as
(
select
f.parent_id,
c.contribution_amount
from
family f
join contribution c on
f.id = c.contributor_id
union all
select
f.parent_id,
cte.contribution_amount
from
cte
join family f on
cte.parent_id = f.id)
select
id,
name,
sum(contribution_amount) as total_contribution
from
cte
group by
id,
name
order by
id;


Change your SQL accordingly. Also, you can add month field to yield results per month.

Re: Reporting by family tree

From
"David G. Johnston"
Date:
On Wed, Oct 25, 2023 at 5:21 AM Ibrahim Shaame <ishaame@gmail.com> wrote:
But what I want to get is  grandfather - father - children:

1 - Grandfather1

3 - father1-1

6 - son1-1

7 – son1-2

4 - Father1-2

8 - son2-1

2 – Grandfather2

5 - Father2-1

etc


Any suggestion



If you want a different ordering of the output change the ORDER BY specification.

Specifically, you want to order by the path of each person.  Since that can only be determined during the traversal you need to create the path data yourself.  I suggest using an integer[] (integer array) to store the path using ID values as breadcrumbs.

David J.

Re: Reporting by family tree

From
o1bigtenor
Date:
On Wed, Oct 25, 2023 at 9:55 AM David G. Johnston
<david.g.johnston@gmail.com> wrote:
>
> On Wed, Oct 25, 2023 at 5:21 AM Ibrahim Shaame <ishaame@gmail.com> wrote:
>>
>> But what I want to get is  grandfather - father - children:
>>
>> 1 - Grandfather1
>>
>> 3 - father1-1
>>
>> 6 - son1-1
>>
>> 7 – son1-2
>>
>> 4 - Father1-2
>>
>> 8 - son2-1
>>
>> 2 – Grandfather2
>>
>> 5 - Father2-1
>>
>> etc
>>
>>
>> Any suggestion
>>
>
> If you want a different ordering of the output change the ORDER BY specification.
>
> Specifically, you want to order by the path of each person.  Since that can only be determined during the traversal
youneed to create the path data yourself.  I suggest using an integer[] (integer array) to store the path using ID
valuesas breadcrumbs. 
>
>
(Not the OP just someone following the list trying to learn.)

'using ID values as breadcrumbs' - - - can I interpret that to mean
the ordinal numbers 1-8 listed (more implied)?

TIA

Re: Reporting by family tree

From
"David G. Johnston"
Date:
On Wed, Oct 25, 2023 at 9:13 AM o1bigtenor <o1bigtenor@gmail.com> wrote:
On Wed, Oct 25, 2023 at 9:55 AM David G. Johnston
<david.g.johnston@gmail.com> wrote:
>
> On Wed, Oct 25, 2023 at 5:21 AM Ibrahim Shaame <ishaame@gmail.com> wrote:
>>
>> But what I want to get is  grandfather - father - children:
>>
>> 1 - Grandfather1
>>
>> 3 - father1-1
>>
>> 6 - son1-1
>>
>> 7 – son1-2
>>
>> 4 - Father1-2
>>
>> 8 - son2-1
>>
>> 2 – Grandfather2
>>
>> 5 - Father2-1
>>
>> etc
>>
>>
>> Any suggestion
>>
>
> If you want a different ordering of the output change the ORDER BY specification.
>
> Specifically, you want to order by the path of each person.  Since that can only be determined during the traversal you need to create the path data yourself.  I suggest using an integer[] (integer array) to store the path using ID values as breadcrumbs.
>
>
(Not the OP just someone following the list trying to learn.)

'using ID values as breadcrumbs' - - - can I interpret that to mean
the ordinal numbers 1-8 listed (more implied)?


Yes, the numbers 1-8 are the values assigned to these 8 example individuals as their unique identifiers.

{1}
{1,3}
{1,3,6}
{1,3,7}
{1,4}
{1,4,8}
{2}
{2,5}

David J.

Re: Reporting by family tree

From
o1bigtenor
Date:
On Wed, Oct 25, 2023 at 5:17 PM David G. Johnston
<david.g.johnston@gmail.com> wrote:
>
> On Wed, Oct 25, 2023 at 9:13 AM o1bigtenor <o1bigtenor@gmail.com> wrote:
>>
>> On Wed, Oct 25, 2023 at 9:55 AM David G. Johnston
>> <david.g.johnston@gmail.com> wrote:
>> >
>> > On Wed, Oct 25, 2023 at 5:21 AM Ibrahim Shaame <ishaame@gmail.com> wrote:
>> >>
>> >> But what I want to get is  grandfather - father - children:
>> >>
>> >> 1 - Grandfather1
>> >>
>> >> 3 - father1-1
>> >>
>> >> 6 - son1-1
>> >>
>> >> 7 – son1-2
>> >>
>> >> 4 - Father1-2
>> >>
>> >> 8 - son2-1
>> >>
>> >> 2 – Grandfather2
>> >>
>> >> 5 - Father2-1
>> >>
>> >> etc
>> >>
>> >>
>> >> Any suggestion
>> >>
>> >
>> > If you want a different ordering of the output change the ORDER BY specification.
>> >
>> > Specifically, you want to order by the path of each person.  Since that can only be determined during the
traversalyou need to create the path data yourself.  I suggest using an integer[] (integer array) to store the path
usingID values as breadcrumbs. 
>> >
>> >
>> (Not the OP just someone following the list trying to learn.)
>>
>> 'using ID values as breadcrumbs' - - - can I interpret that to mean
>> the ordinal numbers 1-8 listed (more implied)?
>>
>
> Yes, the numbers 1-8 are the values assigned to these 8 example individuals as their unique identifiers.
>
> {1}
> {1,3}
> {1,3,6}
> {1,3,7}
> {1,4}
> {1,4,8}
> {2}
> {2,5}
>

Thank you Mr David.