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
inner join (
select category_id, lft, rgt from nested_category
where published = 1
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!
Update: after playing around a lot I've hacked together a query that works, but isn't as efficient as I know it could be.
SELECT nested_category.name, nested_category.category_id, nested_category.lft, nested_category.rgt
LEFT JOIN (
SELECT nested_category.category_id, nested_category.lft, nested_category.rgt
FROM nested_category, (
SELECT category_id, lft, rgt
WHERE published = 0
WHERE nested_category.lft >= notCat.lft
AND nested_category.rgt <= notCat.rgt ) notCat2
WHERE notCat2.category_id IS NULL
ORDER BY nested_category.lft ASC
This returns all the categories that are online, and ignores categories that are online but are "blocked" by a parent being "unpublished" somewhere up the tree.
I'm still a bit hazy on the JOINs so if someone could help me re-write it using JOINs instead of sub-queries I know it'll be more efficient!
I hope this helps someone in future with the MPTT, it's so confusing sometimes!