Подсчет потомков в иерархическом запросе
Мне сказали, что PostgreSQL лучше, чем MySQL, для отображения иерархических данных, поэтому я установил PostgreSQL и готов к работе.
Это схема из моего заголовка (скопирована из 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)
);
Это мое соединение с базой данных и первый запрос:
$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);
Наиболее важными полями таблицы для этого упражнения являются первые четыре (id, taxon, parent а также parent_id, Данные выглядят так:
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
Где последние два ряда представляют волка (Canis lupus) и койота (Canis latrans). В конце концов, я хотел бы иметь возможность отображать имена детей, внуков, родителей, прадедушек и бабушек и т. Д. Но сейчас я просто пытаюсь отобразить количество потомков. Например, если я перешел на MySite / life / млекопитающее, я мог бы увидеть следующий экран:
Orders: 19
Families: 58
Genera: 688
Species: 8,034
Если бы я перешел на MySite / life / canidae, он может отображать что-то вроде этого:
Genera: 6
Species: 37
Может кто-нибудь показать мне лучший способ написать такой запрос и отобразить результаты (с PHP)?
Решение
Учитывая таблицу:
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)
и функция для перевода parent_id в название таксономического ранга:
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;
Вы можете запросить количество потомков с помощью следующего рекурсивного запроса:
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)
Интересная часть внутри with recursive, Первая часть запроса выбирает корневую строку (и) иерархии. Вторая часть (после union all) вызывается рекурсивно и каждый раз добавляет прямых потомков к предыдущему набору результатов. Читать этот чтобы понять, как это работает в деталях.
После того, как иерархия построена, она может быть представлена как вам угодно. В приведенном выше примере показано только количество потомков. Вы также можете получить имена:
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)
То же самое в одной строке:
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)
И так далее…
Другие решения
Других решений пока нет …