MPTtree, Hieararchical trees in a database table

[eluser]TheLoops[/eluser]
And now why to add an nlevel field:

Just compare these functions for getting "A node's descendants for up to 3 levels in depth":

A MPTree-like approach WITHOUT an nlevel field:

Code:

function get_descendants_within_depth($lft,$rgt,$dpth){
        if($rgt - $lft < 3) // leaf node, 3 here because of the possibility of a gap (4 = have children)
            return array();
            
        $result = $this->db->query(
"SELECT node.*
FROM {$this->tree_table} AS node,
    {$this->tree_table} AS parent,
    {$this->tree_table} AS sub_parent,
    (
    SELECT node.{$this->id_col}, (COUNT(parent.{$this->id_col}) - 1) AS depth
        FROM {$this->tree_table} AS node,
            {$this->tree_table} AS parent
        WHERE node.{$this->left_col} BETWEEN parent.{$this->left_col} AND parent.{$this->right_col}
            AND node.{$this->left_col} = {$lft}
        GROUP BY node.{$this->id_col}
        ORDER BY node.{$this->left_col}
    )AS sub_tree
WHERE (COUNT(parent.{$this->id_col}) - (sub_tree.depth + 1)) <= {$dpth} AND node.{$this->left_col} BETWEEN parent.{$this->left_col} AND parent.{$this->right_col}
    AND node.{$this->left_col} BETWEEN sub_parent.{$this->left_col} AND sub_parent.{$this->right_col}
    AND sub_parent.{$this->id_col} = sub_tree.{$this->id_col}
GROUP BY node.{$this->id_col}
HAVING depth > 0
ORDER BY node.{$this->left_col};");
        return $result->num_rows() ? $result->result_array() : array();
    }

Versus an approach WITH an nlevel field:

Code:

function get_descendants_within_depth($lft,$rgt,$dpth){
        if($rgt - $lft < 3) // leaf node, 3 here because of the possibility of a gap (4 = have children)
            return array();
        $level_col = $this->level_col;
        $current_node = $this->get_node($lft);
        $result = $this->MPTtree->get_descendants_where($lft,$rgt,array("{$this->level_col} <=" => $current_node->$level_col + $dpth));
        return $result;
    }