Сумма постов категорий с учетом всех вложенных категорий

233

14 января 2018, 03:01

Хочу на сайте в разделе вывести подразделы с количеством постов в каждом.

Услуги
-- Автоуслуги
---- Автокраны
---- Манипуляторы
-- Коммуникации
---- Водоснабжение
---- Электроснабжение
-- Строительство
---- Фундамент

Т.е захожу в раздел Услуги у меня на странице отображаются подразделы

Автоуслуги 10
Коммуникации 20
Строительство 30

Вчера я использовал MySQL и делал так:

SELECT c.id, c.name, c.alias, COUNT(p.id) AS count
FROM categories c
LEFT JOIN posts p ON p.category_id = c.id
WHERE 
    c.category_id = ?
GROUP BY c.id

Но сегодня, я вдруг осознал, что у меня не считаются посты во вложенных категориях. Т.к. разделы могут быть разной вложенностью, потребуется рекурсивный запрос. Почитав в Интернете, я часто встречал упоминание того, что MySQL не поддерживают подобные запросы. И решил перейти на PostgreSQL. Но так как я с ним не знаком, у меня встала проблема с написанием запроса.

Вот что я только написал:

WITH RECURSIVE r AS (
    SELECT c.id, c.name, c.alias, 0 AS amount
    FROM categories AS c
    WHERE c.category_id = ?
    UNION
    SELECT c.id, c.name, c.alias, amount + COUNT(p.id) AS amount
    FROM r c
    LEFT JOIN posts AS p ON c.id = p.category_id
)
SELECT * FROM r;

Но кажется я сделал не правильно, потому что не вижу перехода на следующий уровень вложенности категорий. Могли бы вы мне помочь с составление правильного запроса?

Answer 1

В общем виде как нибудь так:

with recursive RCTE as(
  select id as root,id,name,parent
    from categories where parent=1
  union all
  select R.root, C.id, R.name, R.parent
   from RCTE R, categories C
  where C.parent=R.id
)
select root, name, count(p.id)
  from RCTE c
  left join posts p on p.category_id=c.id
 group by root, name

Пример на sqlfiddle.com