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