Count descendants in hierarchical query

Question

I was told that PostgreSQL is a better choice than MySQL for displaying hierarchical data, so I installed PostgreSQL and I'm ready to go.

This is the schema from my title (copied from pgAdmin):

CREATE TABLE public.gz_life_mammals (
 id integer NOT NULL,
 taxon text NOT NULL,
 parent text NOT NULL,
 parent_id smallint NOT NULL,
 slug text,
 name_common text,
 plural text,
 extinct smallint NOT NULL,
 rank smallint NOT NULL,
 key smallint NOT NULL,
 CONSTRAINT "Primary Key" PRIMARY KEY (id)
);

This is my database connection and first query:

$dbh = pg_connect("host=localhost dbname=geozoo user=postgres");

if (!$dbh) {
die("Error in connection: " . pg_last_error());
}
$sql = "SELECT * FROM gz_life_mammals";

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

while ($row = pg_fetch_array($result)) {
 echo "ID: " . $row[0] . " | ";
 echo "Taxon: " . $row[1] . " | ";
 echo "ParentID: " . $row[3] . "<br>";
}

// free memory
pg_free_result($result);

// close connection
pg_close($dbh);

The most important table fields for this exercise are the first four (id, taxon, parent and parent_id. The data looks like this:

ID | TAXON         | PARENT    | PARENT_ID
1  | Mammalia      | Chordata  | 1
2  | Carnivora     | Mammalia  | 2
3  | Canidae       | Carnivora | 3
4  | Canis         | Canidae   | 4
5  | Canis-lupus   | Canis     | 5
6  | Canis-latrans | Canis     | 5

Where the last two rows represent the wolf (Canis lupus) and coyote (Canis latrans). Eventually, I'd like to be able to display the names of children, grandchildren, parents, great grandparents, etc. But right now I'm just trying to display the number of descendants. For example, if I navigated to MySite/life/mammalia, I might see the following display:

Orders: 19
Families: 58
Genera: 688
Species: 8,034

If I navigated to MySite/life/canidae, it might display something like this:

Genera: 6
Species: 37

Can anyone show me the best way to write that kind of query and display the results (with PHP)?

See this answer for an example. I think it could help. http://stackoverflow.com/a/5701124 — , Oct 28 '15 at 23:15
Please always provide your version of Postgres. And make it *one* question. The display is a separate issue from the query itself. You also forgot to provide the definition of "Order", "Famlily", etc. — Erwin Brandstetter, Oct 29 '15 at 00:00
Version: 9.5. In the example above, Carnivora is an order. In other words, anything where parent_id = 2 is an order. If parent_id = 3 (e.g. Canidae), it's a family. If parent_id = 4 it's a genus; parent_id = 5...species. Also, my primary question is about the query. Once I have a query, I might be able to figure out how to display the results myself. — , Oct 29 '15 at 00:07

score 0 · Accepted Answer · answered Oct 29 '15 at 10:26

Given the table:

select * from gz_life_mammals;

 id |     taxon     |  parent   | parent_id 
----+---------------+-----------+-----------
  1 | Mammalia      | Chordata  |         1
  2 | Carnivora     | Mammalia  |         2
  3 | Canidae       | Carnivora |         3
  4 | Canis         | Canidae   |         4
  5 | Canis-lupus   | Canis     |         5
  6 | Canis-latrans | Canis     |         5
(6 rows)

and the function to translate parent_id into taxonomic rank name:

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;

you can query number of descendants with the following recursive query:

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;

 tax_rank | num_of_desc 
----------+-------------
 Orders   |           1
 Families |           1
 Genera   |           1
 Species  |           2
(4 rows)

The interesting part is inside with recursive. The first part of the query selects the root row(s) of hierarchy. The second part (after union all) is called recursively and each time adds direct descendants to the previous result set. Read this to understand how it works in details.

After hierarchy is constructed, it can be represented as you like. In the above example only number of descendants are shown. You can get names as well:

with recursive hier(taxon,parent_id) as (
  ...
)
select tax_rank(parent_id),
       taxon as name
from   hier
where  parent_id is not null
order by parent_id;

 tax_rank |     name      
----------+---------------
 Orders   | Carnivora
 Families | Canidae
 Genera   | Canis
 Species  | Canis-lupus
 Species  | Canis-latrans
(5 rows)

The same on one line:

with recursive hier(taxon,parent_id) as (
  ...
)
select tax_rank(parent_id),
       string_agg(taxon,', ') as names
from   hier
where  parent_id is not null
group by parent_id
order by parent_id;

 tax_rank |           names            
----------+----------------------------
 Orders   | Carnivora
 Families | Canidae
 Genera   | Canis
 Species  | Canis-lupus, Canis-latrans
(4 rows)

And so on...

Count descendants in hierarchical query

1 Answers1