Как получить все товары, статус родительской категории которых = true?

Question

[Никита]

Как получить все товары при условии, что самая верхняя категория активна(имеет active = true)?
Есть 2 таблицы: категории и товары. Есть дерево категорий: cat>subcat>subsubcat...и т.д.
Пример: sqlfiddle
Основываясь на данном примере результат должен быть 0 т.к Самая верхняя категория 'Cat1' имеет active 'false'
Я пробовал сделать так:

SELECT * FROM shop_products 
INNER JOIN shop_category as sc1 ON (shop_products.category_id=sc1.id)
INNER JOIN shop_category as sc2 ON (sc1.parent_id=sc2.id)
WHERE sc2.active=1

Но это работает только для одного уровно вложенности.
Надеюсь, вопрос понятен. Заранее благодарен.

Answer 1

[Rsa97]

В таком варианте (Adjacency List) только писать хранимую процедуру, так как MySQL не умеет делать рекурсивные запросы. Можно составить запрос для Nested Set, но вам понадобится перевести свою базу на другую модель организации дерева.
PS. Хотя...
Можно просто наделать LEFT JOIN'ов по максимальному уровню вложенности, взять их значения через IFNULL(..., 1) и сделать AND всех полей. Но такой вариант, мягко говоря, так себе.

Answer 2

[Doc44]

Это тормозное решение в принципе.
Реляционные СУБД плохо переживают работу с иерархией.
Тем более, что, надо полагать, у вас разная глубина вложенности.
Если уж вам так хочется именно малоподходящий для конкретно этой задачи MySQL, то я бы предложил битовые маски (полнотекстовый поиск).

Comments

[Никита]

Не то, что мне так хочется, просто есть уже готовый интернет-магазин на ImageCms. И я пытаюсь найти решение, основываясь на том, что есть.

Answer 3

[retvizan]

В последних версиях есть рекурсивные ОТВ, в ранних придется эмулировать с помощью ранимой процедуры, см статью с универсальным шаблоном процедуры.

Comments

[Никита]

Основываясь на статью, сделал:

CALL WITH_EMULATOR(
"EMPLOYEES_EXTENDED",
"
  SELECT ID, PARENT_ID, ACTIVE, ID AS roott
  FROM shop_category
  WHERE PARENT_ID = 0
",
"
  SELECT C.ID, C.PARENT_ID, C.ACTIVE, C.roott
  FROM shop_category AS P INNER JOIN EMPLOYEES_EXTENDED C ON P.ID = C.PARENT_ID
",
"
  SELECT ID, PARENT_ID, ACTIVE
  FROM EMPLOYEES_EXTENDED
  ORDER BY roott
",
0,
""
)

Возможно, Вы подскажите, как теперь соеденить это с таблицей shop_products для результата?

[Никита]

То есть, мне не много не понятно как соеденить запрос с WITH_EMULATOR

SELECT * FROM shop_products 
INNER JOIN shop_category ON (shop_products.category_id=shop_category.id)
WHERE shop_products.active=1
AND (CALL WITH_EMULATOR(
"EMPLOYEES_EXTENDED",
"
  SELECT ID, PARENT_ID, ACTIVE
  FROM shop_category as sc
  WHERE sc.ID = shop_category.parent_id
",
"
    SELECT P.ID, P.PARENT_ID, P.ACTIVE
    FROM shop_category AS P INNER JOIN EMPLOYEES_EXTENDED C ON C.PARENT_ID = P.ID
",
"
  SELECT ACTIVE
  FROM EMPLOYEES_EXTENDED
  WHERE PARENT_ID = 0
  ORDER BY id
",
0,
""
)) = 1

Так выдает ошибку синтаксиса

Answer 4

[Arris]

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

P.S. Не всегда денормализация таблицы - грех и ересь ;)