Re: Hierarchical Query Question (PHP)

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 and proper 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