From: | Andy Colson <andy(at)squeakycode(dot)net> |
---|---|
To: | David Blomstrom <david(dot)blomstrom(at)gmail(dot)com>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: Hierarchical Query Question (PHP) |
Date: | 2015-10-30 21:32:22 |
Message-ID: | 5633E1E6.60201@squeakycode.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Andy Colson | 2015-10-30 21:36:35 | Re: Hierarchical Query Question (PHP) |
Previous Message | David Blomstrom | 2015-10-30 20:47:06 | Re: Hierarchical Query Question (PHP) |