Всем бобра!
Есть лоты
CREATE TABLE IF NOT EXISTS lots (
lot_id serial PRIMARY KEY,
user_id integer REFERENCES users,
title text,
description text,
price integer,
active boolean DEFAULT FALSE,
created_at timestamptz DEFAULT now(),
category_id integer REFERENCES category (category_id) ON UPDATE CASCADE ON DELETE CASCADE
);
и иерархия категорий.
CREATE TABLE IF NOT EXISTS category (
category_id serial PRIMARY KEY,
name text NOT NULL,
descr text NOT NULL,
parent_id integer NOT NULL
);
ALTER TABLE category
ADD CONSTRAINT category_category_id FOREIGN KEY (parent_id)
REFERENCES category (category_id) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE CASCADE;
Чтобы искать подчинённые категории я делаю запрос через рекурсию:
with recursive category_r_down as (
select category_id, parent_id, name, 1 AS depth
from category
where category_id = 1
union
select category.category_id, category.parent_id, category.name, category_r_down.depth+1 AS depth
from category
join category_r_down
on category.parent_id = category_r_down.category_id
)
select * from lots where category_id in (select category_id from category_r_down ORDER BY depth ASC);
Как сделать выборку всех категорий и подсчитать сколько лотов принадлежат данной категории с учётом подчинённых категорий
select category.*, COUNT(...)
from category
join lots;
Спасибо.