Hierarchical Data

CreativeLogic · 12-08-2007, 04:27 PM

I've come across a problem I can't exactly figure out. The problem at hand is with categories and unlimited subcategories. I've done quite a bit of research on the subject and it seems that Adjacency List Model is the best approach for what I'm needing. The Nested Set Model would be ideal optimizing queries but it supports at most two children and only allows one one parent for each node. That is the problem I'm running into.

The only problem with the Adjacency List Model is the fact that getting all categories and subcategories and grouping them together properly with the children under the proper parents doesn't allow for optimized queries and only works properly using recursion. Below is an example of why it's not optimized:

Code:

function pull_categories ($parent_id = 0)
{
    $categories = array ();
    $categories_query = $this->initialize->db->query ("SELECT * FROM    categories WHERE parent_id = '" . $this->initialize->db->e ($parent_id) . "'");
    while ($category = $this->initialize->db->fetch ($categories_query))
    {
        $categories[$category['category_id']] = $category;
        $categories[$category['category_id']]['children'] = $this->pull_categories ($category['category_id']);
    }
    return $categories;
}

To get around the recursion is gathering all of the information with one loop and storing it in an array then fixing the data that way.

So the question I have for you, does anyone know any other better ways to accomplish what I'm after? Anyone know any data structures or any types of trees that would allow for better optimizing the code?