Mysql, Possible Nested Category problem

Question

I have a mysql question.

I have an application which I am migrating to PHP/Mysql from Coldfusion and MSSql.

There are categories and sub-categories (with apparently unlimited sub-categories).

What is the best way to grab all of the records in a products table given a navigationID.

there are two tables I am dealing with. One is the Products table (which has a nav_id corresponding to --->) and the other is a category relationship table which includes parent/child relationships.

So essentially I want to query the two tables to produce all of the products associated with a navigationID and that NavigationID's sub categories.

any ideas or directions? I know this has to be done. Was thinking the answer would lie in a nested query but cannot wrap my head around it.

Nav_table - contains

ChildNavId      int(11)
ParentNavId     int(11)

Products_Table - contains

productNavID    int(11) // productNavID contains the lowest level NavID

Answer 1

If you can post the schema (or at least the relevant parts) - we can get a bit closer to helping you :)

Answer 2

Sounds like a job for a neatly written recursive algorithm, but as Alex Taylor stated, we would need a schema to be of any use to you.

Answer 3

There are categories and sub-categories (with apparently unlimited sub-categories).

If by this you many there are arbitrarily many levels of category nesting, you can't grab them all with a single query using the ‘parent reference’ schema model. You would have to repeatedly query the children of each category you found, recursively, or just keep doing longer and longer self-joins until you've picked up the most-deeply-nested items.

For single-query access to hierarchical data you will have to look into alternative ways of structuring the schema, principally:

• Nested Sets: stores a numerical ordering of a recursive walk over the tree
• Adjacency Relation: adds extra ancestor/descendent pairs to your child/parent lookup

Either or both of these approaches, in one of their flavours, may work for you. (I usually end up with Nested Sets so that I get an explicit first-to-last order as well.)