Re: Hierarchical Query Question (PHP)

Awesome; thanks!

On Sat, Oct 31, 2015 at 7:19 AM, Andy Colson <andy(at)squeakycode(dot)net> wrote:

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

--
David Blomstrom
Writer & Web Designer (Mac, M$ & Linux)
www.geobop.org