Как вывести товары по дереву категорий?

Question

[Алексей]

Всем привет! Есть к примеру дерево категорий:
- транспорт (1)
-- автомобили (2)
--- легковые (5)
--- грузовые (3)
-- мотоциклы (4)
--- спорт (7)
--- кросс (6)

В скобках - это условные ID категории

Предположим пользователь открыл категорию с ID = 1 (транспорт).
Как в этом случае вывести все товары которые находятся в этой категории и во всех дочерних по дереву? Ведь товар хранит в себе только ID той категории в которой он находится непосредственно. т.е. если товар лежит в категории "грузовые", то в таблице `ads` в поле `category_id` хранится цифра 3.

Я думал рекурсивно получить все категории под ID 1 потом их подкатегории потом следующих и т.д. Собрать это все в один массив, прогнать его через $tree_category = implode(',', $array_cats); и вытащить примерно таким запросом:

SELECT * FROM `ads` WHERE `category_id` IN($tree_category);

Но это все ерунда наверное? куча запросов и одному богу известно сколько там получится запятых в $tree_category

Answer 1

[entermix]

Иерархические (рекурсивные) запросы

Answer 2

[d-stream]

Нормальный подход:
запросить дерево в виде плоского списка (join дерева самого на себя по паренту) и к этому запросу приджойнить уже товары

конкретный запрос зависит от конкретной структуры категорий
так что как минимум надо бы привести структуру этой таблицы
(хотя почти не гадая - это id, parent_id, описание )

Comments

[Алексей]

Не совсем понял, можете чуть подробнее описать?
У меня получается так:

SELECT c1.*, c2.* FROM `category` c1
JOIN `category` c2 ON c2.`parent_id` = c1.`id`

Но в таком случае вытаскиваются абсолютно все категории которые есть в таблице, это никак не поможет на мой взгляд

[d-stream]

Евгений: добавляем условие where c1.category=<нужная категория> or c2.parent=<нужная категория> и получаем только нужную категорию из с1 и дочерние категории

ну и join должен быть left
плюс такое прокатит для двухуровневого дерева

для дерева любой степени разлапистости - удобнее всего использовать рекурсивные cte (хз умеет ли MySQL такое), либо извращаться с временной таблицей и наполнять ее "по слоям"

[Алексей]

d-stream: Спасибо! Да, с mysql тут проблема... решил таки хранить в каждом товаре путь до категории (1-2-5) и выбирать товары примерно по такому запросу:

SELECT * FROM `tovar` WHERE `path` = '{$cat_id}' OR `path` LIKE '{$cat_id}-%'

Надеюсь такой способ имеет право на жизнь

[d-stream]

Евгений: имеют право любые способы, но некоторые могут оказаться "неустойчивыми".

Структура дерева с id имеет фишку, что его ветки можно легко менять передвигать, переподчинять и если в остальном живут только референсы на это дерево - то больше ничего нигде менять не придется.

если же в товаре будет жить что-то типа цепочки дерева - то эту цепочку надо будет менять при каждом изменении дерева...

[Алексей]

d-stream: Тут да, в таком случае цепочку нужно хранить не в самом товаре, а в категории, к которой он привязан, тогда запрос получится:

SELECT `tovar`.* FROM `tovar`
LEFT JOIN `category` ON `category`.`id` = `tovar`.`id` 
WHERE `category`.`path` = '{$cat_id}' OR `category`.`path` LIKE '{$cat_id}-%'

Выходит просто один лишний JOIN, а при изменении расположения категории придется поменять только её цепочку...
Во, точно, так и сделаю...
Мне кажется что в данной ситуации это пока единственный оптимальный вариант как по нагрузке, так и по удобству.
Спасибо еще раз за помощь... Дали хорошего пинка к размышлениям ;)

[d-stream]

Евгений: безусловно структура категорий - должна храниться в категориях, а товары - связываться исключительно по id

остается только запрос на получение списка id категории и id всех ее подкатегорий.

как я уже говорил - хз как живет MySQL с CTE, но вот попалось: mysqlserverteam.com/mysql-8-0-labs-recursive-commo...