Hey everyone. Last year I found Thunder’s Nested Set class, implemented as a model.
Thunder’s original class was great, but there were a few things I felt were lacking.
Some basic info before proceeding:
- This class implements Joe Celko’s Nested Set data model, also known as MPTT or Modified Preorder Tree Traversal.
- Nested Sets are great for storing hierarchical data in a database.
- This data model is a better alternative to the Adjacency List model.
Changes:
* Now implemented as a Library as opposed to Model (but could easily be converted back if needed)
* PHP 4 support dropped
* CI db class & active record used as extensively as possible, replacing ALL manual SQL queries
* Table prefixes now supported
* Code cleanup (i.e. - single quotes now replace double quotes when possible)
* Added support for a “parent” column (i.e. - parent_id), enables a DBA to easily
see which child values belong to which parent values, gives ability to speedily
request immediate children only, etc. (plans in the future to expand on this)
* Multiple root nodes now supported. The original class assumed only 1 root node
existed in a table, now multiple roots can exist (or you can view the table itself
as the root node, with multiple direct children allowed)
Attempts were made to keep this class compatible with Thunder’s original class,
and it is compatible in almost every way, EXCEPT for the following:
- getRoot no longer exists, now getRootNodes is required, which returns an array of nodes
- parent column is now expected to exist, default name is “parent_id”, type should be integer
The library is attached to this post.
Example DB structure:
CREATE TABLE `nested_set_tree` (
`id` int(10) unsigned NOT NULL auto_increment,
`lft` int(10) unsigned NOT NULL default '0',
`rgt` int(10) unsigned NOT NULL default '0',
`parent_id` int(10) unsigned NOT NULL default '0',
PRIMARY KEY (`id`),
KEY `lft` (`lft`),
KEY `rgt` (`rgt`),
KEY `parent_id` (`parent_id`)
) ENGINE=MyISAM;
Example basic PHP usage:
<?php
class Example {
public function __construct() {
$this->load->library('nested_set');
# If you are using nested_set in multiple locations, it's best to create a new instance
# Using original instance
$this->nested_set->setControlParams('nested_set_tree');
# Using new instance
$this->new_nested_set = new Nested_set();
$this->new_nested_set->setControlParams('nested_set_tree2');
# Alternatively, you can use the original $this->nested_set for multiple tables,
# but you must call setControlParams before each separate table use
$this->nested_set->setControlParams('nested_set_tree');
$root_nodes1 = $this->nested_set->getRootNodes();
$this->nested_set->setControlParams('nested_set_tree2');
$root_nodes2 = $this->nested_set->getRootNodes();
}
}
?>
I already have this class in use with Backend Pro. Had to modify Backend Pro of course, and also modified KHACL to use this class (as KHACL stores it’s data using nested sets, but was managing the data itself, instead of using a dedicated class).
Works great so far.
Let me know if you have any questions/comments.
