recursion?

[nicholasstephan]

ok - I've got a bit of a puzzle here. I've got a client that is insisting on like 15 levels (ok, only 4) of navigation for an online store. And that's not even constant across the site. For example: You've got a Furniture section; in that you have Bedroom, Bathroom, etc..; in that you have Sofas, Tables, etc...; inside that you have Slipcovers, Sleepers, etc... etc... etc..

 

But then garden furniture only is only 2 levels deep. Accessories only 1. So what I'm thinking is to make a generic sections table with a parent_id field and make a tree; Furniture would have an id of 1 and a parent of null (signifying a top level section), Bedroom would be a child of Furniture... you get the idea.

 

The puzzle is in how to find products for each section. I want to be able to query all products in Bedroom, for example. A Sofa (for example) will have a section_id linking it to Sleepers, which is within Sofas, which is in Bedroom. So I have to somehow query: Every product with a section_id = the Bedroom section's id, or any child of Bedroom's id, or any child of that's id...

 

I recognize this as a recursive loop and could probably do it by finagling some php, but that seems horribly inefficient. How would I got about it with in MySQL? Do I have to create a procedure, or is there some trick to it?

 

 

Sorry for the wordy post. Any help would be appreciated.

 

 

Thanks

 

 

Nick

[akitchin]

i would guess that with this setup, the most straightforward method would be with PHP, given the amount of control structures required. have you investigated the preorder tree traversal structure for your data? MySQL has a good article on it. it makes querying for the data you're after a much simpler affair, but of course it requires that you restructure your model.

 

i'm ready to be chastised for saying this, but i think for using the old parent_id structure, using either PHP or SQL would have similar performance if you simply manage your database connections and resultsets wisely.

[gwolgamott]

You may want to use a recursive function calling your entire database (Assuming you do as you say and structure your database a tree) and storing each level in array and it's child is an array who has a child thats an array... so on and so forth until you hit an ending level.

 

Now this is not exactly what you want but if you can understand it then you can change it to fit your needs.... A very loose SUDO code to at least get you to where you are trying to head I think?

<?php
function Load_Tree($path, $stopper)  
{
    if (Table is open) {           
        while ((while we are reading the table and something is there )!== false) 
        {
            if (the piece of data is a parent to a child) 
            {
               	$array[$element] = Load_Tree(recursive run function to get child of last element since it was a parent);
            } 
            elseif (this is truly a child then) 
            {
                $array[] = $element; //insert data
            }
        }
    }
    return (isset($array) ? $array : false);
}
?>

[Psycho]

I would simply associate each product with all four levels of groupings ass applicable. So, let's assume you have four tables for each level:

 

Section 1 (e.g. Furniture)

Section 2 (e.g. Living Room)

Section 3 (e.g. Sofas)

Section 4 (e.g. Sleepers)

 

Every lower section will have a parent ID field that relates it to the next higher level (i.e. Sofas will be associated with Living Room). So, you don't have to have child records for each possible parent record.

 

Then, for your products, have four separate fields to identify the relationship between the product and all four levels of sections. If you have a product that is related to records at sections 1 & 2, then the fields for sections 3 & 4 will remain NULL.

 

So, if you need to find all products related to Furniture, just query all products where the ID in the section1 field is the same as the ID for furniture.

 

Now, if you need to relate products to multiple values int eh same section it gets more complicated, But, you didn't state thar requirement.

[akitchin]

I would simply associate each product with all four levels of groupings ass applicable. So, let's assume you have four tables for each level:

 

Section 1 (e.g. Furniture)

Section 2 (e.g. Living Room)

Section 3 (e.g. Sofas)

Section 4 (e.g. Sleepers)

 

Every lower section will have a parent ID field that relates it to the next higher level (i.e. Sofas will be associated with Living Room). So, you don't have to have child records for each possible parent record.

 

Then, for your products, have four separate fields to identify the relationship between the product and all four levels of sections. If you have a product that is related to records at sections 1 & 2, then the fields for sections 3 & 4 will remain NULL.

 

So, if you need to find all products related to Furniture, just query all products where the ID in the section1 field is the same as the ID for furniture.

 

Now, if you need to relate products to multiple values int eh same section it gets more complicated, But, you didn't state thar requirement.

 

the problem with this structure is, what if you want to suddenly associate a product with five sections? you'd have to add a column, and if the database is designed properly, you should never need to change the structure itself after starting to use it.

[nicholasstephan]

the problem with this structure is, what if you want to suddenly associate a product with five sections? you'd have to add a column, and if the database is designed properly, you should never need to change the structure itself after starting to use it.

 

I'd agree with this. It might make sense to limit this client to four levels, and build the database accordingly... but if possible I'd rather build something more generic. Another trick up my sleeve that I can pull out of my hat if I need it again.

 

akitchin: that article is SWEET! The only problem I'm seeing with the Nested Set model is in ordering the nodes. The structure is so dependent on the nodes' placement relative to each other that it seems like it would be difficult to alphabetize the list by name or price, for example.

 

I've also found this: http://www.artfulsoftware.com/mysqlbook/sampler/mysqled1ch20.html

 

Am reading it now, so not sure if it applies, but it looks promising....

[nicholasstephan]

You may want to use a recursive function calling your entire database (Assuming you do as you say and structure your database a tree) and storing each level in array and it's child is an array who has a child thats an array... so on and so forth until you hit an ending level.

 

Now this is not exactly what you want but if you can understand it then you can change it to fit your needs.... A very loose SUDO code to at least get you to where you are trying to head I think?

<?php
function Load_Tree($path, $stopper)  
{
    if (Table is open) {           
        while ((while we are reading the table and something is there )!== false) 
        {
            if (the piece of data is a parent to a child) 
            {
               	$array[$element] = Load_Tree(recursive run function to get child of last element since it was a parent);
            } 
            elseif (this is truly a child then) 
            {
                $array[] = $element; //insert data
            }
        }
    }
    return (isset($array) ? $array : false);
}
?>

 

 

pretty cool. I might default to this. I'd like to keep as much of it in the database as I can... this method would either require pulling the whole database into a php array and then recurse through it, or there'd be a huge number of sql queries. seems like a lot of overhead either way.

[hamza]

the problem with this structure is, what if you want to suddenly associate a product with five sections? you'd have to add a column, and if the database is designed properly, you should never need to change the structure itself after starting to use it.

 

very interesting example ..............

do u have more example reading for more better understanding.

 

I'd agree with this. It might make sense to limit this client to four levels, and build the database accordingly... but if possible I'd rather build something more generic. Another trick up my sleeve that I can pull out of my hat if I need it again.

 

akitchin: that article is SWEET! The only problem I'm seeing with the Nested Set model is in ordering the nodes. The structure is so dependent on the nodes' placement relative to each other that it seems like it would be difficult to alphabetize the list by name or price, for example.

 

I've also found this: http://www.artfulsoftware.com/mysqlbook/sampler/mysqled1ch20.html

 

Am reading it now, so not sure if it applies, but it looks promising....

[Psycho]

the problem with this structure is, what if you want to suddenly associate a product with five sections? you'd have to add a column, and if the database is designed properly, you should never need to change the structure itself after starting to use it.

 

Did you read ALL of my post? I covered that in the last paragraph. While it would be advantageous to create it such that it *could* be modified int he future to handle multiple associations, the fact is that was not stated as a requirement and this solution would be easier to implement. I see way too may people that don't know how to JOIN tables properly - if at all. So, the less JOINs needed the better.

 

BUt, if you want such a solution, fine...

 

Create one table for all the categories. Each category record will have a column to identify the parent category - if one exists. Then the product table would have no direct associattions at all. Instea you would have an intermidiary table to make the associations between products and categories. You could then associate a product to any number of categories and sub categories. So, if product X is associated with subCategory C (level 3), you would also associate it with the parent subCategory B (level 2) and that category's parent, category A (level 1/parent).

 

 

Then by JOINing the three tabels you could get all the products associated with a category at any level.