Re: Hierarchical Query Question (PHP) - Mailing list pgsql-general

On 10/30/2015 05:10 PM, David Blomstrom wrote:
> Just so I understand what's going on, I can create a lookup table by pasting this code...
>

I don't know anything about biology so this data might be laughable, but its based on your original question:

http://stackoverflow.com/questions/33402831/count-descendants-in-hierarchical-query


It seemed like the parent_id column was really the rank, and I couldn't tell if it should be taxon_rank or parent_rank.
Thinking more about the problem, I normalized a little, renamed some, and came up with this script.  It lacks indexes
andproper names, etc, etc, because I wanted to tackle one problem at a time.  Hopefully its a good example. 

create table rank (
    id integer,
    descr text
);

insert into rank values(1, 'Classes');
insert into rank values(2, 'Orders');
insert into rank values(3, 'Families');
insert into rank values(4, 'Genera');
insert into rank values(5, 'Species');

create table mammals (
    id integer,
    taxon text,
    rank integer,
    parentid integer
);

insert into mammals values (0, 'Chordata',      1, null);
insert into mammals values (1, 'Mammalia',      1, 0);
insert into mammals values (2, 'Carnivora',     2, 1);
insert into mammals values (3, 'Canidae'  ,     3, 2);
insert into mammals values (4, 'Canis'    ,     4, 3);
insert into mammals values (5, 'Canis-lupus',   5, 4);
insert into mammals values (6, 'Canis-latrans', 5, 4);


-- This query shows you the basic results.  It only
-- returns the id columns.  further queries build on this base one.
--  (you could this of this query as Order Chordata :-) )
with recursive heir(id, rank, parentid) as (
    select m.id, 0, m.parentid
    from mammals m
    where taxon = 'Canis'
    union all
    select m.id, m.rank, m.parentid
    from heir
    inner join mammals m on m.parentid = heir.id
)
select * from heir;

Results:
  id | rank | parentid
----+------+----------
   4 |    0 |        3
   5 |    5 |        4
   6 |    5 |        4
(3 rows)


----
-- This looks up the columns for a more meaningful result:
with recursive heir(id, rank, parentid) as (
    select m.id, 0, m.parentid
    from mammals m
    where taxon = 'Canidae'
    union all
    select m.id, m.rank, m.parentid
    from heir
    inner join mammals m on m.parentid = heir.id
)
select m.taxon, r.descr
from heir
inner join mammals m on m.id = heir.id
inner join rank r on heir.rank=r.id

Results:
      taxon     |  descr
---------------+---------
  Canis         | Genera
  Canis-lupus   | Species
  Canis-latrans | Species


---------
-- This, finally, groups and counts, like your original question
with recursive heir(id, rank, parentid) as (
    select m.id, 0, m.parentid
    from mammals m
    where taxon = 'Canidae'
    union all
    select m.id, m.rank, m.parentid
    from heir
    inner join mammals m on m.parentid = heir.id
)
select r.id, r.descr, count(*)
from heir
inner join mammals m on m.id = heir.id
inner join rank r on heir.rank=r.id
group by r.id, r.descr
order by r.id


Results:
  id |  descr  | count
----+---------+-------
   4 | Genera  |     1
   5 | Species |     2