Nested Model Menu on E-Commerce Site

I have a Database with a category table using the Nested Approach in MySql. But I keep hitting a wall. The Site contains "sections" species (As this is a pet store). I already have a query that returns all the categories that have products in them for each species.

Now my problem is that I want to be able to only show categories that have products under them. So for example if I create the category food with a child adult. Then the system should not list the category und开发者_运维技巧er any species. But as soon as I create a Product under the adult category the system should list Food with the child Adult .

The Table is currently structured as follows:

Category:

• id
• top
• name
• lft
• rgt
• created (for the sake of completeness)
• lastupdated (for the sake of completeness)

So I am able to build a full tree from the table for each species but I need the categories to only be visible once they have products in them for the species.

Sounds simple enough but for some reason I can't get my brain around this. Any Help? What would be the best way to approach this?

Update:

The query I'm using to get the Categories:

SELECT node.id as nid, node.top as top, node.*, brands.name as brandname from
brands, foods, foodcategories, categories as node, categories as parent WHERE 
node.lft BETWEEN parent.lft AND parent.rgt AND parent.top=1 AND 
foodcategories.food=foods.id AND foodcategories.category=node.id 
AND brands.id=foods.brand AND foods.species={speciesid}

I then execute a query to get all the categories and their lft,rgt. Which I can built the tree from.

Something like this should work:

$q = mysql_query("SELECT a.* FROM Categories a WHERE a.id IN (SELECT categoryID FROM Products)");

OR

$q = mysql_query("SELECT a.* FROM Categories a WHERE (SELECT count(*) FROM Products WHERE categoryID = a.id) >= 1");  

You will need to match up your fields and write the rest of the code to output, but this should get you started. You also will want to change a.* to list all your fields being used for better optimization.

EDIT:

I understand what you are trying to accomplish I think, but still don't understand your database structure. For 1 level of subcategories, I would do something like:

echo '<ul>';
$q = mysql_query("SELECT a.* FROM Categories a, SubCategories b WHERE a.ID = b.categoryID AND (SELECT count(*) FROM Products WHERE SubCategoryID = b.id) >= 1");
while($f = mysql_fetch_array($q)) {
    echo '<li>'.$f['Category'];
    $qsc = mysql_query("SELECT b.* FROM SubCategories b WHERE (SELECT count(*) FROM Products WHERE SubCategoryID = b.id) >= 1");
    if (mysql_num_rows($qsc) > 0) {
        echo '<ul>';
    }
    while($fsc = mysql_fetch_array($fsc)) {
        echo '<li>'.$fsc['SubCategory'].'</li>';
    }
    if (mysql_num_rows($qsc) > 0) {
        echo '</ul>';
    }   
    echo '</li>';   
}
echo '</ul>';

You can take this and adapt to your code or if you explain a little more about your database structure, I can help further.

After a couple of days struggling a finally thought of a solution and implemented it.

I can execute the following query and get the entire path to and from a category,

(SELECT cast(group_concat(parent.id SEPARATOR '-') AS char) as catpath
            FROM categories AS node,
                    categories AS parent
            WHERE node.lft BETWEEN parent.lft AND parent.rgt
                    AND node.id=categories.id
            ORDER BY node.lft )

then I thought if I could this why not include this path in the category query. So when I retrieve the categories that have products in them I can simply parse this string and match the id to a list of categories that I also retrieve.

This is quite a bit of looping. So I cache the menu and only generate it once every 5 min.

So that's it! 3 Queries!

• One for the list of species.
• One for all the categories that have products in them.
• One for all the categories that I loop to find one with a certain ID.

On to the next challenge !