(MySQL) Агрегатные функции в модели MPTT / Nested Set

Question

Поэтому я использую MySQL и сохраняю свою структуру категорий, основанную на модели обхода дерева вложенных наборов / модифицированных предварительных заказов, в имени таблицы "nested_category", которая имеет поля:category_id, name, lft, rgt, published

published это либо 1, либо 0... Если это 1, то категория будет отображаться на живом сайте. Если это 0, то он не будет отображаться на живом сайте, и что более важно, любые дети из этой неопубликованной категории также не будут отображаться на живом сайте.

У меня проблемы с написанием запроса, чтобы перечислить все категории, которые имеют published=1и игнорировать все категории, которые являются потомками категории, которая имеет published=0,

На данный момент я использую:

SELECT category_id, lft, rgt FROM nested_category WHERE published = 1

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

Я также пытаюсь связать это с моей таблицей "new_products", которая имеет поля: product_id, name, stock, price, category_id, так что я могу написать запрос, чтобы выбрать все продукты, которые имеют published=1 и являются частью "опубликованной" категории. Я получил это далеко:

select @myRight := rgt, @myLeft := lft 
from nested_category where name="ELECTRONICS";

select productId, productName, new_products.category_id, 
price, stock, new_products.published 
from new_products 
inner join ( 
    select category_id, lft, rgt from nested_category 
    where published = 1
) cat 
on new_products.category_id = cat.category_id 
and cat.lft >= @myLeft 
and cat.rgt <= @myRight 
and new_products.published = 1 
order by productName asc

Поскольку в приведенном выше запросе используется мой первый запрос, он не возвращает никаких "неопубликованных" категорий или продуктов, но он не учитывает, когда "опубликованная" категория является потомком "неопубликованной" категории. Надеюсь, что это имеет смысл!

Answer 1

Немного улучшен с глубиной узлов:

SELECT node.name, node.category_id, node.lft, node.rgt, (COUNT(parent.name) - 1) AS depth
FROM nested_category as node
LEFT JOIN (
    SELECT nested_category.category_id, nested_category.lft, nested_category.rgt 
    FROM nested_category, (
        SELECT category_id, lft, rgt 
        FROM nested_category 
        WHERE published = 0
    ) notNodeCat 
    WHERE nested_category.lft >= notNodeCat.lft 
    AND nested_category.rgt <= notNodeCat.rgt ) notNodeCat2
ON notNodeCat2.category_id=node.category_id,
nested_category as parent
LEFT JOIN (
    SELECT nested_category.category_id, nested_category.lft, nested_category.rgt 
    FROM nested_category, (
        SELECT category_id, lft, rgt 
        FROM nested_category 
        WHERE published = 0
    ) notParentCat 
    WHERE nested_category.lft >= notParentCat.lft 
    AND nested_category.rgt <= notParentCat.rgt ) notParentCat2
ON notParentCat2.category_id=parent.category_id            
WHERE notNodeCat2.category_id IS NULL AND notParentCat2.category_id IS NULL AND node.lft BETWEEN parent.lft AND parent.rgt
GROUP BY node.name
ORDER BY node.lft ASC

Answer 2

Ладно, после того, как я поигрался с ЛОТОМ, я взломал что-то, что работает...

SELECT nested_category.name, nested_category.category_id, nested_category.lft, nested_category.rgt 
FROM nested_category 
LEFT JOIN (
    SELECT nested_category.category_id, nested_category.lft, nested_category.rgt 
    FROM nested_category, (
        SELECT category_id, lft, rgt 
        FROM nested_category 
        WHERE published = 0
    ) notCat 
    WHERE nested_category.lft >= notCat.lft 
    AND nested_category.rgt <= notCat.rgt ) notCat2
ON notCat2.category_id=nested_category.category_id 
WHERE notCat2.category_id IS NULL
ORDER BY nested_category.lft ASC

Он отображает все элементы nested_category, которые имеют published=1 которые не заблокированы родительским существом published=0

Одна вещь, в которой мне нужна помощь, - переписать ее, используя больше левых соединений, так как я слышал, что они более эффективны!