PHP MySql вывод товаров по категориям
Есть 3 таблицы.
catalog_categories = Содержит id, name .... (категории товаров)
products = Содержит id, name, img....
catalog_relations = Содержит cid, pid, type{
type{
1 = продукт
2 = категория
}
pid = id родительской категории
cid = id категории или товара
}
Тоесть таблица catalog_relations содержит связи. Причина ее наличия - товар может принадлежать нескольким категориям, так и категория может пренадлежать нескольких другим категориям. Из-за чего получается каша.
Как мне вывести максимально производительно продукты категории, чтобы я указывал id категории он сам искал категории его дочернии элементы и выводил продукты всего этого.
Не получается построить правильный sql запрос для этого
Для начала мне нужно получить список id всех дочерних элементов категории, тоесть найти все cid, у которых pid равен 0 нулю допустим, а потом рекрсивно найти у них потомков
CREATE TABLE `catalog_relations` (
cid int(11) NOT NULL COMMENT 'Дочерний элемент',
pid int(11) NOT NULL COMMENT 'Родительский элемент',
type tinyint(4) NOT NULL DEFAULT '1' COMMENT '1 - продукт, 2 - категории, 3 - модификации'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
--
-- Дамп данных таблицы catalog_relations
INSERT INTO catalog_relations (cid, pid, type) VALUES
(1, 0, 2),
(18, 1, 2),
(19, 18, 2),
(20, 19, 2),
(21, 19, 2);
CREATE TABLE catalog_categories (
id int(11) NOT NULL,
url varchar(255) NOT NULL,
name varchar(150) NOT NULL,
title varchar(150) NOT NULL,
templates varchar(99) NOT NULL DEFAULT 'category',
time_creation int(11) NOT NULL,
active int(11) NOT NULL,
sort int(11) NOT NULL DEFAULT '1',
is_nav tinyint(4) NOT NULL DEFAULT '0',
sort_nav int(11) NOT NULL DEFAULT '9999',
last_modified int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
-- Дамп данных таблицы catalog_categories
INSERT INTO catalog_categories (id, url, name, title, templates, time_creation, active, sort, is_nav, sort_nav, last_modified) VALUES
(1, 'catalog', 'Каталог', 'Каталог', 'category', 1593723600, 1, 2, 1, 3, 1630052113),
(18, 'dveri-i-furnitura', 'Двери и фурнитура', 'Двери и фурнитура', 'category', 1626037200, 1, 25, 0, 0, 1636391443),
(19, 'furnitura-dvernaya', 'Фурнитура дверная', 'Фурнитура дверная', 'category', 1626037200, 1, 9999, 0, 0, 1630093856),
(20, 'dovodchiki', 'Доводчики', 'Доводчики', 'category', 1626100822, 1, 9999, 0, 0, 1626100822),
(21, 'evrocilindry', 'Евроцилиндры', 'Евроцилиндры', 'category', 1626100823, 1, 9999, 0, 0, 1626100823);
CREATE TABLE `products` (
id int(11) NOT NULL,
url varchar(255) NOT NULL,
prefix_url varchar(255) NOT NULL,
name varchar(150) NOT NULL,