So I'm using MySQL and storing my category structure based on the Nested Set / Modified Pre-Order Tree Traversal model, in a table named "nested_category" which has fields: category_id, name, lft, rgt, published
published is either 1 or 0... If it's 1 then the category will show upon the live site. If it's 0 then it won't show on the live site, and more importantly any children of that unpublished category won't show on the live site either.
I'm having problems writing a query to list all the categories that have published=1, and ignore all categories that are descendants of a category which has published=0.
At the moment I'm using:
SELECT category_id, lft, rgt FROM nested_category WHERE published = 1
I really can't work out how to get it to ignore "child" categories when a parent is "unpublished".
I'm also trying to link this to my "new_products" table which has fields: product_id, name, stock, price, category_id, so that I can write a query to select all products that have published=1 and are part of a "published" category. I have got this far:
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
Since the above query uses my first query, it doesn't return any "unpublished" categories or products, but it doesn't take into account when a "published" category is a descendant of an "unpublished" category. Hope this makes sense!
