MySQL Nested Sets Searching

I'm really struggling with a nested set query in our PHP/MySQL driven shop, I'll use electronics parts as an example.

Categories are stored as a nested set model (left, right and depth).

As the customer navigates through the store, they can filter the list of products by brand, category, price range, etc.

Lets say a customer is viewing all products by Sony. Sony would have produ开发者_运维问答cts in Computing, Televisions, Audio and Hosuehold Appliances.

But the Sony products aren't stored in those categories (but they could be), they are stored in subcategories of those main categories, such as:

Televisions > LCD > Widescreen
Televisions > CRT
Computing > Optical Drives > DVD-RW
Computing > Input Devices > Wireless > Keyboards
Audio > Portable > MP3
Household Appliances

After the customer has selected Sony, I want them to have the option to narrow down by category, so they would initially have these choices:

Televisions
Computing
Audio
Household Appliances

But I can only do a query that brings back a list of the specific categories:

Widescreen
CRT
DVD-RW
Keyboards
MP3
Household Appliances

I need the main categories to show, then if the customer selects Televisions, they would get the choices:

LCD
CRT

Can anyone help me with this, preferably returning the information as quick as possible, with minimal recursion (hence the use of nested sets in the first place) or too many queries, I would be very grateful.

Here is the query I use to get a list of subcategories if it helps with the structure of the database:

select      c.categories_id, cd.categories_name, c.parent_id, c.lft, c.rgt, c.dpth
from        categories c
inner join  categories_description cd on cd.categories_id = c.categories_id
inner join  products_to_categories p2c on p2c.categories_id = c.categories_id
inner join  products p on p.products_id = p2c.products_id
where       cd.language_id='1'
and         c.lft between 3489 and 3670
and         c.categories_status = '1'
group by    c.categories_id
order by    sort_order, cd.categories_name

As the database was originally an adjacancy model structure, each category id also has the parent ID stored as well if that simplifies anything.

Thanks.

---

From your comment you have tried something else but I thought I would answer anyway.

As you found out the nested set model is superb at retrieving nodes and leafs fast and efficiently. However one gotchya is that you found that it is difficult to order the list after the insertion.

I have overcome this in the past by either a) ordering on the client side (difficult) and then caching or b) making sure I insert the node/leaf so it is automatically ordered (or even use up/down links so the admin can order themselves AFTER the insert has happened.

I personally like option 2 and have never found it to be a problem.