MPTtree Model: Database Error

[eluser]lightnb[/eluser]
I'm trying to use the MPTtree Tree Traversal library from Martin Wernstahl.

I've got this in my controller:

Code:

$
this->load->model('mpttree', 'MPTtree');
$this->MPTtree->set_opts(array( 'table' => 'Forums_Categories', 'left' => 'LeftID',
                       'right' => 'RightID', 'id' => 'UID',
                       'title' => 'categhoryname'));
$tree = $this->MPTtree->tree2array();
print_r($tree);

and I'm getting the error:

SELECT * FROM Forums_Categories WHERE LeftID BETWEEN AND ORDER BY LeftID ASC

Any ideas?

[eluser]m4rw3r[/eluser]
It seems like it cannot find a root node. Do you have a root node in your tree?
Had forgot to add a check to if the root was found in tree2array (everything had worked for me, but I never tested it without data in the table). I have now added a checking of the root node to my dev copy of MPTtree:
Paste this after line 834, before the comment that says query:

Code:

if($node == false)
            return false;

Then tree2array will return false if the node to be root in the query doesn't exists.

[eluser]lightnb[/eluser]
[quote author="m4rw3r" date="1206378734"]It seems like it cannot find a root node. Do you have a root node in your tree?[/quote]

Thanks, the database was the problem. I had been trying a different Tree Traversal Library before, and it looks like it had messed up all the left and right values in the database.

Is there a function in your library that can print a hierarchy indented list of topic names? (Or one that returns a depth value and a topic name in an associative array?) I'd like to get a human readable list of the topics like:

Code:

Bird
   Parrot
   Robin
Food
   Pizza
      Pepperoni
      Cheese
   Cat

[eluser]m4rw3r[/eluser]
Currently there are a very simple display() method, just prints an HTML list like this:

Code:

root node<br />
&nbsp;&nbsp;&nbsp;&nbsp;page 1<br />
&nbsp;&nbsp;&nbsp;&nbsp;page 2<br />
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;page 3<br />
&nbsp;&nbsp;&nbsp;&nbsp;page 4<br />
&nbsp;&nbsp;&nbsp;&nbsp;...

But I think you want something more customizable (with links and so on)?
You could write your own by using tree2array and recursive functions, but what specifications do you want this list to have?
So can I make a (somewhat) generic customizable method.

[eluser]lightnb[/eluser]
I'm thinking something like this:

Code:

function GetCategoryArray()
    { // Function gets a list of all categories in the category table
    // and returns them as an associative array
        
        $this->db->select('node.ForumCategoryName, (COUNT(parent.ForumCategoryName) - 1) AS Depth, node.UID, node.ForumCategoryType As Type');
        $this->db->from('Forums_Categories AS node, Forums_Categories AS parent');
        
        $Where = "node.LeftID BETWEEN parent.LeftID AND parent.RightID AND node.LeftID != 1";
        $this->db->where($Where);
        
        $this->db->group_by('node.ForumCategoryName');
        $this->db->order_by('node.LeftID');
        $Query = $this->db->get();

        $i = 0;
        foreach($Query->result() as $row)
        {
            $Categories[$i]['Name'] = $row->ForumCategoryName;
            $Categories[$i]['ID'] = $row->UID;
            $Categories[$i]['Depth'] = $row->Depth;
            $Categories[$i]['Type'] = $row->Type;
            $i++;
        }
        
        return $Categories;
    }

and then in the view you can do:

Code:

foreach($Categories as $Category)
{
    $Spacing = '';
    for($i=0; $i<$Category['Depth']; $i++)
    {
        $Spacing .= "-- &nbsp;";
    }
    
    echo '<div>' . $Spacing . anchor("/forums/topics/". url_title($Category['Name'], 'underscore')) .'</div>';
}

[eluser]m4rw3r[/eluser]
If you want to have a depth column, use the get_descendants() method. As of 0.1.6 it has got a third argument, $with_level_col (default = false), that if set to true makes get_descendants() return a multidimensional array with table data with a depth column.

So it seems like get_descendants() performs exactly what you want, the only thing I can see you have to change is the 'Depth' to 'depth' to use the view with get_descendants() :-).

[eluser]lightnb[/eluser]
Thanks! Got it working by creating this function in my controller:

Code:

function GetCategoryArray()
    { // Function gets a list of all categories in the forum category table
    // and returns them as an associative array,
        
        $this->load->model('mpttree', 'MPTtree');
        $this->MPTtree->set_opts(array( 'table' => 'Forums_Categories',
                     'left' => 'LeftID',
                     'right' => 'RightID',
                    'id' => 'UID',
                    'title' => 'categoryname'));
        $Categories = $this->MPTtree->get_descendants(1,10,true);
        return $Categories;
    }

Two stupid questions... the left id will always be one, but the right will change based on the number of nodes in the tree. Is there a function or preferred method of getting the right value, other than hard codding it?

Second, is there a way to put this function in my model? CI seems to "freak out" when i try to load a model from a model...

Thanks again!

[eluser]m4rw3r[/eluser]
Formula for calculating the expected (the tree can contain gaps and duplicates) right value of a node:

Code:

R = L + 2 * D + 1
where
L = left value
R = expected right value
D = number of descendants to the node with left value L

I cannot use this formula in the model, because I don't know how many children the node has in the first place.
So you have to hard code the number of children to the root or, you can fetch the root first and then load the descendants:

Code:

$root = $this->MPTtree->get_node(1); // you can vary this number to change which node to be the root of the menu
$Categories = $this->MPTtree->get_descendants($root['LeftID'],$root['RightID'],true);

Formula for calculating the number of descendants of a node (used by count_descendants, so just use count_descendants (but it could be fun to know )):

Code:

D = (R - (L + 1)) / 2
where
L = left value
R = right value
D = number of descendants to the node with left value L and right value R

I have also looked at the problem about loading models within models, and the only solution I've found is to use get_instance() to get the CI object and then use that directly when loading and calling other models (for some reason I can't call another model, that already has been loaded by the controller, in a model (I have to use the CI object instead of $this)).