SiteCrafting, Inc.

Usually, it's a bad idea to allow elements in a tree to be parents of themselves, because that creates problems with trying to add other children to those elements, or even listing out the tree's structure.

But, sometimes things have to be their own parent. For example, if you want to report details of an element's children to the parent element, what happens at the top level of the tree? You can't outright exclude a tree element from the reporting, you can't create a placeholder element because that would skew the tree, and you can't add in a special case in your code base for that single item.

Well, you could, but you don't want to. You'd rather have a simple and elegant solution that doesn't require lots of special cases for tree levels, and that doesn't require maintenance. So what do you do?


Consider this function:

    function recursive_fetch($parent=0) {
        $parent = intval($parent);

        $sql = "
            SELECT * 
            FROM recursive_table
            WHERE parent=$parent
        ";

        $results = select($sql);

        foreach($results as $key => $result) {
            $results[$key]['children'] = recursive_fetch($result["id"]);
        }

        return $results;
    }    

If you ran this query, it would build a hierarchical tree of all the elements in the table, starting with 0 (meaning top level), and going all the way down to the bottom of the tree.

This function assumes, of course, that the tree is valid and perfect. No element can be a parent of itself, or it just wouldn't show up at all.

However, with a simple tweak to the where clause in the query, this can be fixed by checking the value of the parent.

        $sql = "
            SELECT *
            FROM recursive_table
            WHERE 
                (parent = $parent AND id != parent)
                OR
                (0 = $parent AND id = parent)
        ";

So when $parent is 0 (aka the first call of the function), the second portion of the where clause is "(0 = 0 AND id = parent)". So the query fetches all the items in the table that have a parent of 0, and any row that is its own parent. When the parent is explicitly set on subsequent calls of the function, "(0 = 1 AND id = parent)" is false, so any items where id=parent are excluded. The first part of the where clause takes over, and "(parent = 1 and id != parent)" fetches all items in the table that are not parents of themselves.

There are other ways to make this work, but the only (other) way that I can think of using a single query is with the IF statement.

The limit of this method is that it doesn't detect cycles (A -> B -> C-> A) in the tree, just when an element is a parent of itself.