Имеется следущая структура базы данных:
CREATE TABLE cats (
id BIGSERIAL PRIMARY KEY,
parent_id BIGINT REFERENCES cats(id) ON DELETE CASCADE,
name TEXT NOT NULL UNIQUE
);
CREATE TABLE products (
id BIGSERIAL PRIMARY KEY,
category BIGINT REFERENCES cats(id) ON DELETE CASCADE
);
CREATE TABLE fields (
id BIGSERIAL PRIMARY KEY,
name VARCHAR(255) NOT NULL UNIQUE
);
CREATE TABLE properties (
product_id BIGINT NOT NULL REFERENCES products(id) ON DELETE CASCADE,
field_id BIGINT NOT NULL REFERENCES fields(id) ON DELETE CASCADE,
value TEXT NOT NULL DEFAULT '',
UNIQUE (product_id, field_id)
);
Т.е. существует таблица содержащая id продукта и относящая его в определённую категорию (либо без категории если category = NULL). Категории в таблице cats имеют иерархическую структуру. Т.е. например:
- Computers (id = 1, parent_id = null)
---- Motherboards (id = 2, parent_id = 1)
---- Processors (id = 3, parent_id = 1)
-------- Intel Processors (id = 4, parent_id = 3)
-------- AMD Processors (id = 5, parent_id = 3)
-------- Other Processors (id = 6, parent_id = 3)
---- Hard Drives (id = 7, parent_id = 1)
---- Network Cards (id = 8, parent_id = 1)
- Instruments (id = 9, parent_id = null)
---- Hammers (id = 10, parent_id = 9)
---- Screwdrivers (id = 11, parent_id = 9)
Предположим некий продукт относится к категории AMD Processors (id = 5). Соответственно products.category = 5.
Вопрос 1: как подсчитать количество продуктов относящихся к категории Computers (id = 1). Т.е. количество продуктов у которых products.category может быть: 1, 2, 3, 4, 5, 6, 7, 8. Т.е. требуется подсчитать количество продуктов которые относятся либо напрямую к категории «Computers» либо к одной из подкатегорий этой категории? Понятно что «SELECT COUNT(*) FROM products WHERE products.category = 1» здесь не сработает т.к. products.category = 5 (относится к конкретной подкатегории).
Кроме того с каждым продуктом может быть ассоциировано различное количество свойств с помощью таблицы properties.
Например если имеются в таблице fields следущие записи:
(id = 1, name = 'Наименование')
(id = 2, name = 'Описание')
(id = 3, name = 'Вес')
Вопрос 2: как подсчитать количество продуктов для которых например Наименование = 'XXX' И Вес = 'YYY'? Плюс объёдинить это в один запрос с запросом из Вопроса 1?
