Thread: Hierarchical Query Question (PHP)

On 10/29/2015 7:18 PM, David Blomstrom wrote:
> Can anyone tell me how to write the query described @
> http://stackoverflow.com/questions/33402831/count-descendants-in-hierarchical-query
> ?
>
> The answer's very thorough, but I don't know how to string two queries
> and a function together like that. This doesn't work:
>
> $sql = "select * from gz_life_mammals;";
>
> create function tax_rank(id integer) returns text as $$
>      select case id
>               when 1 then 'Classes'
>               when 2 then 'Orders'
>               when 3 then 'Families'
>               when 4 then 'Genera'
>               when 5 then 'Species'
>             end;
> $$ language sql;
>
> $sql = "with recursive hier(taxon,parent_id) as (
>    select m.taxon, null::integer
>    from gz_life_mammals m
>    where taxon='Mammalia' --<< substitute me
>    union all
>    select m.taxon, m.parent_id
>    from hier, gz_life_mammals m
>    where m.parent=hier.taxon
> )
> select tax_rank(parent_id),
>         count(*) num_of_desc
> from   hier
> where  parent_id is not null
> group by parent_id
> order by parent_id;";
>
> Thanks.
>

The function is created once (like with your create tables).  Don't use
it in PHP.

Your PHP should only be like:

 > $sql = "with recursive hier(taxon,parent_id) as (
 >    select m.taxon, null::integer
 >    from gz_life_mammals m
 >    where taxon='Mammalia' --<< substitute me
 >    union all
 >    select m.taxon, m.parent_id
 >    from hier, gz_life_mammals m
 >    where m.parent=hier.taxon
 > )
 > select tax_rank(parent_id),
 >         count(*) num_of_desc
 > from   hier
 > where  parent_id is not null
 > group by parent_id
 > order by parent_id;";

$result = pg_query($dbh, $sql);

while ($row = pg_fetch_array($result)) {
    etc
    etc


-Andy

On 10/30/2015 3:47 PM, David Blomstrom wrote:
> No, I get the same T_FUNCTION error.
>
> Someone commented that the function...
>
>    create function tax_rank(id integer) returns text as $$
>      select case id
>               when 1 then 'Classes'
>               when 2 then 'Orders'
>               when 3 then 'Families'
>               when 4 then 'Genera'
>               when 5 then 'Species'
>             end;
>    $$ language sql;
>
> ...should ideally be part of the table schema. Does that mean I need to
> go into pgAdmin, open up my table and paste this in somehow?
>
> I wonder if the function is even necessary. My goal is to create a
> hierarchical query that displays the number of children, grandchildren,
> etc. And, depending on the taxonomic level, it might display the result
> as "20 families, 74 genera and 413 species." With MySQL I could probably
> turn that into a series of echo values, which I could then display like
> this:
>
> echo ''.$NumberChildren.' families<br>
> '.$NumberGrandchildren.' genera<br>
> '.$NumberGreatgrandchildren.' species';
>
> I'm wondering if I should figure out this query or go back to square one
> with a simpler query. I've never seen a query with a function before. ;)
>
> Thanks for the tips.

Shoot, I should have read this before responding to the first one.

Yes, create function tax_rank, should be done in pgAdmin.

 > I wonder if the function is even necessary.

Correct, its not.  It should probably be a lookup table:

create table taxon (
   taxonid serial,
   descr text
);
create table gz_life_mammals (
   id serial,
   taxonid integer, -- use the lookup table
   parentid integer -- use the lookup table
);


-Andy

On 10/30/2015 4:36 PM, Andy Colson wrote:
> On 10/30/2015 3:47 PM, David Blomstrom wrote:
>> No, I get the same T_FUNCTION error.
>>
>> Someone commented that the function...
>>
>>    create function tax_rank(id integer) returns text as $$
>>      select case id
>>               when 1 then 'Classes'
>>               when 2 then 'Orders'
>>               when 3 then 'Families'
>>               when 4 then 'Genera'
>>               when 5 then 'Species'
>>             end;
>>    $$ language sql;
>>
>> ...should ideally be part of the table schema. Does that mean I need to
>> go into pgAdmin, open up my table and paste this in somehow?
>>
>> I wonder if the function is even necessary. My goal is to create a
>> hierarchical query that displays the number of children, grandchildren,
>> etc. And, depending on the taxonomic level, it might display the result
>> as "20 families, 74 genera and 413 species." With MySQL I could probably
>> turn that into a series of echo values, which I could then display like
>> this:
>>
>> echo ''.$NumberChildren.' families<br>
>> '.$NumberGrandchildren.' genera<br>
>> '.$NumberGreatgrandchildren.' species';
>>
>> I'm wondering if I should figure out this query or go back to square one
>> with a simpler query. I've never seen a query with a function before. ;)
>>
>> Thanks for the tips.
>
> Shoot, I should have read this before responding to the first one.
>
> Yes, create function tax_rank, should be done in pgAdmin.
>
>  > I wonder if the function is even necessary.
>
> Correct, its not.  It should probably be a lookup table:
>
> create table taxon (
>    taxonid serial,
>    descr text
> );
> create table gz_life_mammals (
>    id serial,
>    taxonid integer, -- use the lookup table
>    parentid integer -- use the lookup table
> );
>
>
> -Andy
>
>
>
>

Humm, after looking at this further, my answer isn't right.  I did not
notice rank (classes, orders, families...) is different than taxon
(mammilia, carnivora, ...)

But still, lookup table is better than function.

-Andy

Just so I understand what's going on, I can create a lookup table by pasting this code...

create table taxon (
  taxonid serial,
  descr text
);
create table gz_life_mammals (
  id serial,
  taxonid integer, -- use the lookup table
  parentid integer -- use the lookup table
);

...into pgAdmin III, right? (I can't use the shell/terminal at the moment, and it will be at least a few weeks before I can get it fixed.) And this script will create TWO tables - gz_life_mammals and a matching "lookup table"?

Just so I understand what's going on, I can create a lookup table by pasting this code...

create table taxon (
  taxonid serial,
  descr text
);
create table gz_life_mammals (
  id serial,
  taxonid integer, -- use the lookup table
  parentid integer -- use the lookup table
);

...into pgAdmin III, right? (I can't use the shell/terminal at the moment, and it will be at least a few weeks before I can get it fixed.) And this script will create TWO tables - gz_life_mammals and a matching "lookup table"?

On 30/10/2015 22:10, David Blomstrom wrote:
> Just so I understand what's going on, I can create a lookup table by
> pasting this code...
>
> create table taxon (
>   taxonid serial,
>   descr text
> );
> create table gz_life_mammals (
>   id serial,
>   taxonid integer, -- use the lookup table
>   parentid integer -- use the lookup table
> );
>
> ...into pgAdmin III, right? (I can't use the shell/terminal at the
> moment, and it will be at least a few weeks before I can get it fixed.)
> And this script will create TWO tables - gz_life_mammals and a matching
> "lookup table"?

Yes, it will. I haven't seen, what went before in this thread so may
have missed stuff, but you should also add a foreign key constraint
between the tables (for taxonid anyway, dunno what parentid refers to):

create table gz_life_mammals (
   id serial,
   taxonid integer, -- use the lookup table
   parentid integer, -- use the lookup table

  constraint mammals_taxon_fk foreign key (taxonid) references
taxon(taxonid)

);


If parentid also references taxon.taxonid, add a similar constraint for
it too,

Ray.


--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie

On 30/10/2015 22:29, David Blomstrom wrote:
> Thanks for the tips.
>
> In pgAdmin III, I can create a table step-by-step by choosing Edit >
> Object > New Table
>
> But is there a pace for me to past in a block of code that creates the
> table with just one process? If I click on the SQL icon, a SQL window
> opens up, and I can past the code into SQL Editor, but I don't know how
> to "activate" it; pressing ENTER does nothing.

There's an "Execute query" button in the toolbar, and pressing F5 does
the same. If the toolbar isn't visible, you can make it visible under
the "View" menu.

As a bonus, you can select a block of SQL and only the selected code
will get executed.

Ray.

--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie

On 10/30/2015 03:29 PM, David Blomstrom wrote:
> Thanks for the tips.
>
> In pgAdmin III, I can create a table step-by-step by choosing Edit >
> Object > New Table
>
> But is there a pace for me to past in a block of code that creates the
> table with just one process? If I click on the SQL icon, a SQL window
> opens up, and I can past the code into SQL Editor, but I don't know how
> to "activate" it; pressing ENTER does nothing.

http://pgadmin.org/docs/1.20/query.html

The green arrow, menu Query --> Execute or F5, take your pick.

>
> I'm following a pgAdmin tutorial, but this page appears to focus on the
> terminal instead...
>
> http://pgadmin.org/docs/1.4/pg/tutorial-table.html


--
Adrian Klaver
adrian.klaver@aklaver.com

Ah, yes - "Execute SQL." It created the table this time. Awesome.

One other question - when I close the SQL window, it asks me if I want to save the file. Is there any special reason for saving it? It looks like it simply saved a copy of the query I executed.

On Fri, Oct 30, 2015 at 3:36 PM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:

On 10/30/2015 03:29 PM, David Blomstrom wrote:

Thanks for the tips.

In pgAdmin III, I can create a table step-by-step by choosing Edit >
Object > New Table

But is there a pace for me to past in a block of code that creates the
table with just one process? If I click on the SQL icon, a SQL window
opens up, and I can past the code into SQL Editor, but I don't know how
to "activate" it; pressing ENTER does nothing.

http://pgadmin.org/docs/1.20/query.html

The green arrow, menu Query --> Execute or F5, take your pick.

I'm following a pgAdmin tutorial, but this page appears to focus on the
terminal instead...

http://pgadmin.org/docs/1.4/pg/tutorial-table.html

--
Adrian Klaver
adrian.klaver@aklaver.com

--

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

The field descr would presumably hold the values I originally had in the field Taxon - e.g. the names of various taxons, like 'Mammalia' and 'Canis-lupus.' The field id is just a numerical key, and I already have the numerical values for parentid.

But what am I supposed to put in the field taxonid?

The field descr would presumably hold the values I originally had in the field Taxon - e.g. the names of various taxons, like 'Mammalia' and 'Canis-lupus.' The field id is just a numerical key, and I already have the numerical values for parentid.

But what am I supposed to put in the field taxonid?

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