A little more work, and a little more discussion with Daniel Khan finally has my tools for interacting with PEAR::DB_NestedSet complete. There’s a mistake in the documentation for the moveTree method that had me thrown for a while. Since it’s taken a while to get to grips with this module, I thought a summary of how I’ve used it might be in order.
Please note that this is a fairly hastily put together piece, which does not delve into all the intricacies of the package but should give an experienced PHP coder with a basic understanding of the theory behind nested sets with the information they need to get up and running. For some backgrounf on nested sets I’d recommend this article.
Configuration and Instantiation
To use the module with a database, you will need a table with (mostly integer) fields for at least:
- Node ID
- Node Parent
- Left
- Right
- Order_Number
- Level
- Name (I use a varchar for this one)
and another field for category locks so that NestedSet can make changes withour worrying about simultaneous changes confusing matters. Mine has the following fields:
- lockId (char(32))
- lockTable (char(32))
- lockStamp (int(11))
I’m initially using the module to represent a navigation menu so I also added a ‘url’ field to allow me flexibility in where each link points, and a ‘permissions’ field so I can determine which of my users can see a given node. I defined how my field names match the required ones my setting up an array:
$table_match = array( 'id' => 'id', 'parent' => 'rootid', 'lft' => 'l', 'rght' => 'r', 'order_num' => 'norder', 'level' => 'level', 'name' => 'name' );
The key for each array entry is the database field name I’ve used and the value assigned to it is the name the module prefers.
The one other value we need is the DSN for accessing our database. eg:
$dsn = 'mysql://user:password@host/database'
With all of those in place we can start up our object:
$nestedSet =& DB_NestedSet::factory('DB', $dsn, $table);
You also need to tell NestedSet which tables in the database it is using. Mine are called ‘categories’ and ‘cat_locks’, so I tell it to use those with:
$nestedSet->setAttr(array( 'node_table' => 'categories', 'lock_table' => 'cat_locks' ));
With all that in place, $nestedSet is ready for us to interact with.
Adding Nodes
NestedSet supports storing multiple ‘trees’ within one database table, each of which can have its own root node. To create a root node we use the following method:
$parent = $nestedSet->createRootNode(array('name' => 'Home'), false, true);
This creates a root node with the name ‘home’ (we can set other attributes by including them in the array, and returns it as an object in $parent.
We can add sub-nodes with:
$subnode = $nestedSet->createSubNode($parent, array('name' => 'Sub Node'));
This returns the sub-node as an object in $subnode. Adding further subnodes is as simple as repeating this method with $parent replaced with whichever object you want this node to be a child of. So to add another element which is a sibling of “Sub Node” we would use:
$siblingnode = $nestedSet->createSubNode($parent, array('name' => 'Sub Node Sibling'));
and to add a child of that node we could use:
$third = $nestedSet->createSubNode($siblingnode, array('name' => 'Level 3'));
NestedSet takes care of all the database access for you, so once you’ve made these calls there’s no need to worry about telling it to store your values.
Changing Node Properties
Once you have your nodes created, you may well want to add or change some of the information associated with them. In order to do that we can make use of the setAttr() method. Say we wanted to change the name of a node with id = 3:
$ournode = $nestedSet->pickNode(3);
$changes = $nestedSet->setAttr(array('name' => 'A new name'));
Deleting Nodes
To delete a node, we want to make use, unsurprisingly, of the deleteNode() method. All the method requires is the numeric ID of the node that you want to delete. ie.
$delete = $nestedSet->deleteNode(2);
The method returns “true” if the node is successfully deleted, and “false” otherwise. Remember that deleting a node could “orphan” any “children” it might have had, so use with caution.
Moving/Copying Nodes
Rather than employ a moveNode() method to move single nodes, which if used wrongly could leave orphaned nodes all over the place, the developers of NestedSet have implemented the moveTree() method which allows you to copy or move a node and all of its children in one go.
moveTree() takes four arguments:
- id of the node you want to move/copy
- name of the “target” node
- a constant defining how you want the new position to relate to that target node
- optionally a boolean saying you want to copy rather than move the tree
The method offers three constants to describe how you want to make the move:
NESE_MOVE_BEFORE, NESE_MOVE_AFTER, NESE_MOVE_BELOW
So in other words, if you want to move node number 4 (and any of its children) to be a child of node 2 you would use:
$move = $nestedSet->moveTree(4, 2, NESE_MOVE_BELOW);
If instead of moving it you wanted to copy, and instead of making it a child you wanted to make it a sibling coming next in order, you would use:
$copy = $nestedSet->moveTree(4, 2, NESE_MOVE_AFTER, 1);
Producing Output
Before we can produce output, we need to specify exactly which nodes we want to output. If we want all the nodes from all the trees in the database, we would use:
$data = $nestedSet->getAllNodes(true);
To retrieve all nodes within the tree that $id is a member of we would use:
$data = $nestedSet->getBranch($id, true);
And to get all the descendants of node $id:
$data = $nestedSet->getSubBranch($id,true);
This produces an associative array called $data containing all of the nodes. Just as with any associative array we can iterate through it in a variety of ways and make any changes we want, so say we wanted to add a “url” field based on the name of each node we could use:
foreach ($data as $id => $node) {
$data[$id]['url'] = "/option/".urlencode($node['name']);
}
NestedSet supports a number of “output drivers” or mechanisms for connecting with other tools to produce our output. The most commonly used option is the “Menu” driver which can be used with PEAR::HTML_Menu to output HTML representations. To use that we first need to define some parameters eg:
$params = array( 'structure' => $data, 'titleField' => 'name', 'urlField' => 'url' );
Here “structure” is the associative array with our data in it, and “titleField” and “urlField” are the keys in that array for the title and URL of each node.
With that array in place we have a few more steps to produce our output. Firstly, we want to get an Output object from NestedSet. This will allow us to access the data in a method that is useful for our different output systems. We use a factory method with the parameters we just defined and the name of the driver we want to use:
$output =& DB_NestedSet_Output::factory($params, 'Menu'); $structure = $output->returnStructure();
At this point, $structure will contain a “nested” array which looks rather like the structure of our tree. You might like to try:
print_r($structure);
to take a look.
If you want to make use of HTML_Menu, this is the data you pass on to it:
$menu =& new HTML_Menu($structure, 'sitemap');
should do the trick.
Since I tend to use Smarty for all my templating, I have taken to assigning $structure into smarty and then use a custom plugin to iterate over it and produce the menus. You can find that plugin here.
Conclusion
Once you’re up and running with NestedSet it provides a simple way of maintaining tree structures and using them in web applications. While it’s not the appropriate choice in all contexts (I wouldn’t choose it if I were using XML to store my data, for example) it’s well worth a little investigation.
Hi, found your page through google. Im using DB_NestedSet to store menu structures in it and would be interested in your smarty plugin, but the link doesnt work. Im currently handling the tree exactly like you have described in your tutorial [it’s a pity that you didnt write that some months earlier :)]. Just now im considering to drop the extra Menu instance. Do you think its really used in that context? I don’t know how your smarty code looks, but in my case, its parsing the whole structure anyway. Well, let me hear what you think.
Regards
Andre
Thanks for the comment. The link should be working now. I haven’t had a chance to experiment with varying the code much, but I’ll have a go at dropping the extra Menu instance and see how things work out.
Sorry, the URL still returns a 404.
Oops again. Broken link, and broken filename! Try it now.
Thanks a lot for writing this, it’s very helpful. This looks like the perfect package for an application I’m writing but….
I do have a small problem: For some reason, when I use this package, when I try to insert data into the db, I get an SQL error that seems to be caused by my insert array keys being set with apostrophe characters.
In other words,my inserts end up looking like this:
INSERT INTO _tablename_ (‘id’,’field1′,’field2′) values (‘0′,’1′,’2′);
instead of this:
INSERT INTO _tablename_ (id,field1,field2) values (”,’1′,’2’);
The former coughs out an error. The later works fine. Can someone PLEASE tell me what I’m doing wrong?!?!?!? Thanks.
Is there any particular reason you’re inserting directly into the database? I’ve found it’s easiest to use the class methods to do all the inserts rather than use raw SQL, as that ensures data consistency.
Personally, I never use apostrophes around integers when inserting into MySQL.
I’m actually not trying to insert directly into the db via any method outside of the package–Since it started throwing errors, I turned on bebugging, and it displays the sql syntax error. That error is showing the quotes.
In an effort to debug, I’ve tried to manually insert into the Db. When I take the error directly and remove the quotes from the field names and try to manually insert into the DB, then there’s no problem.
If that makes any sense…..
Also: I never use quotes around intergers either: Again, this seems to be something tacked on to the query without any interaction from me.
Hi. Found your tutorial also useful. DB_NestedSet is a great package, the only problem is the lack of documentation. Next Saturday in the Third Hungarian PHP Conference I’m going to say some word about this project in my presentation. Hope more will recognize then its strength.
Hi: Thanks to anyone who considered my question. The answer seems to be in my version of the package. (I was using the beta 1.3.6 release.) Nothing I did with that package seemed to work, but the 1.2.4 version works really well. (Could be plenty of reasons for that, but I don’t have time for that question now that it works.) Thanks for the great discussion and exploration of this topic.
How do i add from an adminsitration interface a subnode, leaving all the structure intact ?? I’m using the coolmenu from the pear package and the database is genereted when the script is run…but how do i add a single subnode ?
simistef – I’ve not used coolmenu at all, but I do have an administration interface through which I can add nodes. Basically, you need to load the NestedSet and to know the parent to which you want to add this child. Then you can simply use the commands I talk about above, such as:
$subnode = $nestedSet->createSubNode($parent, array(‘name’ => ‘Sub Node’));
Hi,
Your article came up in google search for administration interface for DB_NestedSet. But I can’t find any example or explanation of administration interface for it, through which I can add, remove, edit or move nodes. I am in the situation where I got menu to display but that is very little use to me if I can’t add,remove, move or edit that menu items. Would you share your administration interface please?
Many thanks
@rawson This article is now five years old and I’ve not used DB_NestedSet in about four, so I’m afraid I can’t really help. You might be best off asking on the pear-general email list?
I’m completely stumped.
I cannot rename a tag (this is in a class);
function renameTag($id, $name)
{
$node = $this->nestedSet->pickNode($id);
if($node == FALSE) {return ‘Error, tag selection failed’; }
$node = $this->nestedSet->setAttr(array(‘STRNA’=>$name));
if($node == FALSE) {return ‘Error, tag rename failed’; } else {return $node; }
}
I pick the node, I try to edit the node, but it just doesn’t work. What am I doing wrong?
Matt: You wanted method updateNode() instead of setAttr(). This code should work:
function renameTag($id, $name)
{
$node = $this->nestedSet->updateNode($id, array(’STRNA’=>$name));
if($node == FALSE) {return ‘Error, tag rename failed’; } else {return $node; }
}
Good summary. I found your site via Google. Interesting problem I’m having is moveTree() with cloning. It would appear as though it only moves the top node and orphans the children.
Anyhow, thanks for this
Hi,
Great article. Really helped me out. But now I’m stumped:
Say I have additional fields (besides the default) in my nested table and I want to be able to access those additional fields in a nested structure.
To get the nodes in the their default structure I:
$data = $nestedSet->getAllNodes(true);
But the only way I can convert that array structure to a true nested structure is by running through DB_NestedSet_TreeMenu or DB_NestedSet_Output. However, this strips out my custom fields and only leaves me with the essentials (presumably the essential fields needed to create a menu).
Essentially I want to be able to access the $nestedSet->getAllNodes(true) as a nested set array while keeping my custom fields accessible.
Any recommendations?
Thanks!