Re: Hierarchical Query Question (PHP)

["Jason O'Donnell" <odonnelljp01@xxxxxxxxx>]

David,

Does wrapping the transaction with BEGIN; COMMIT; work as you would expect?

$sql = "BEGIN; 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; COMMIT;";

On Thu, Oct 29, 2015 at 8:18 PM, David Blomstrom <david.blomstrom@xxxxxxxxx> 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.

--

Jason O'Donnell

Crunchy Data Solutions