Welcome Guest, Not a member yet? Register   Sign In
Nested Set library (aka MPTT, aka Hierarchy DB Trees)
#21

[eluser]WanWizard[/eluser]
What do you mean exactly by 'this library'? The only one I can find is the one in the first post, and that doesn't include a primary_key_column_name.

The parent_id is needed when you're using what is called the Modified Preorder Tree Traversal. So next to the left- and right pointers, you also have a direct link to the parent node in the tree. This can save some queries, depending on the implementation.
#22

[eluser]CroNiX[/eluser]
From Nested_set.php
Code:
public function setControlParams($table_name, $left_column_name = 'lft', $right_column_name = 'rgt', $primary_key_column_name = 'id', $parent_column_name = 'parent_id') {
        $this->table_name = $table_name;
        $this->left_column_name = $left_column_name;
        $this->right_column_name = $right_column_name;
        $this->primary_key_column_name = $primary_key_column_name;
        $this->parent_column_name = $parent_column_name;
    }
#23

[eluser]CroNiX[/eluser]
I have a huge tree stored using the nested set storage method (primary id, left id, right id). In addition, I also have a type_id which indicates what type of item each node is. There is a certain type of node which can only be a child.

What I am trying to do with a single query is retrieve all of these children (easy since they all have the same type) but then I also want the name of one (or more) of the parents that are of a certain type (category if you will).

Think of a hierarchy of
-Country (type_id = 0)
--State (type_id = 1)
---City (type_id = 2)

A city will always be a child node. I am trying to get a list of cities, with their associated state name (there may be other cases where I need additional parents, such as the Country. Basically, I want to retrieve the tree in "reverse order".

City, State
City, State
...

This is my modified "getTreePreorder" function (which returns node level and depth info in a single query too...useful if you are using code to build html from a tree)
Code:
function getTreePreorder($node, $extra_cols = array(), $type = 6)
    {
        extract($this->_get_table_setup_data($node));
        $extra_select = $this->prep_cols('node', $extra_cols);
        
        $q = "SELECT node.`$pkcol`, node.`$leftcol`, node.`$rightcol`, $extra_select,
                (COUNT(parent.`$leftcol`) - 1) AS depth,
                CEIL((node.`$rightcol` - node.`$leftcol` - 1) / 2) AS child_quantity
            FROM
                `$table` AS node,
                `$table` AS parent
            WHERE node.`$leftcol` BETWEEN parent.`$leftcol` AND parent.`$rightcol`
            GROUP BY node.`$leftcol`
            HAVING node.`$leftcol` >= $leftval AND node.`$rightcol` <= $rightval
            ORDER BY node.`$leftcol`";
        $query = $this->db->query($q);
        
        $treeArray = array();
        
        foreach($query->result_array() AS $result)
        {            
            if($result['type_id'] <= $type)
            {
                $treeArray[] = $result;
            }
        }
        
        return array(  "result_array"  => $treeArray,
                       "prev_left"     => $node[$leftcol],
                       "prev_right"    => $node[$rightcol],
                       "level"         => -2);
    }

Here are 2 helper functions used above:
Code:
//helper function gets the default column names and protects identifiers, as well as any optional column names provided in $extra_cols
    //$type can be 'node' or 'parent', depending on what is needed for the query
    function prep_cols($type = 'node', $extra_cols = array())
    {
        $cols = array();
        $extra_select = '';
        
        foreach($this->default_col_names as $d)
        {
            $cols[] = "$type.`$d`";
        }
        foreach($extra_cols as $d)
        {
            $cols[] = "$type.`$d`";
        }
        $extra_select = implode(', ', $cols);
           //if( ! empty($extra_select)) $extra_select = ', ' . $extra_select . ', ';
          
           return $extra_select;
    }
    
    //helper function to get the needed table and column info for various db queries.
    //returned array is meant to be extract()ed internally to use the variables in building SQL
    function _get_table_setup_data($node)
     {
         return array(
             'table'      =>       $this->table_name,
             'pkcol'         =>          $this->primary_key_column_name,
            'leftcol'    =>       $this->left_column_name,
            'rightcol'   =>       $this->right_column_name,
              'pkval'         =>    (int) $node[$this->primary_key_column_name],
            'leftval'    => (int) $node[$this->left_column_name],
            'rightval'   => (int) $node[$this->right_column_name]
        );
     }

And this is a piece of code that will retrieve the siblings of a given node. This does what I need, but I need it incorporated into my original query or else this query will get executed several thousand times. (Also great for creating breadcrumbs).
Code:
//retrieve ancestor nodes (branch) of the hierarchy for a supplied node, or a specific type of node from the branch.
    //if $hierarchy_type_id contains id of specific hierarchy type,
    //it will return only that node instead of the entire branch.
    //eg. enter the type id for state to find out what state a node is in.
    function get_branch($node, $hierarchy_type_id = '', $extra_cols = array())
    {
        extract($this->_get_table_setup_data($node));
        
        $extra_select = $this->prep_cols('parent', $extra_cols);
        
        $having = (empty($hierarchy_type_id)) ? '' : " HAVING parent.`type_id` = $hierarchy_type_id ";
        
        $q = "SELECT
                $extra_select
            FROM
                `$table` AS node,
                `$table` AS parent
            WHERE
                node.`$leftcol` BETWEEN parent.`$leftcol` AND parent.`$rightcol` AND
                node.`$pkcol` = $pkval
            $having
            ORDER BY
                parent.`$leftcol`";
        
        return $this->db->query($q)->result_array();
    }

Any guidance would be greatly appreciated. I'm not very good with these more complex queries.
#24

[eluser]mrbinky3000[/eluser]
Hello, just wondering if anyone has uploaded the most recent version of this wonderful library class to this thread. I only found the one attached file on the first post of this thread. Is that the most current version?

Thanks in advance.
#25

[eluser]Unknown[/eluser]
Is thunder's version most complete? there isn't anything else thats complete, library or model class for nested sets.

TIA.
#26

[eluser]arlong[/eluser]
in my controller(i added library on autoload):
$this->nested_set->setControlParams('nested_set_tree');
$root_nodes1 = $this->nested_set->getRootNodes();
i SEE this error ":
Fatal error: Call to undefined method CI_DB_mysql_driver::getwhere() in C:\wamp\www\application\libraries\Nested_set.php on line 322"


ANDDD
how i can add a new data to database this system?
#27

[eluser]WanWizard[/eluser]
Find another solution.

Appearantly the nested sets post in the wiki is very dated, the getwhere() method was deprecated a long time ago, and is no longer present in CI 2.x.

You could have a look at Datamapper, it includes a nested sets extension (http://datamapper.wanwizard.eu/pages/ext...dsets.html) which is still in beta, but seems to work just fine...
#28

[eluser]Unknown[/eluser]
Anyone can upload the zip file of this library ?
The download link in first page doesn't work.

BTW, switch it back to a model is a good idea.
#29

[eluser]Glazz[/eluser]
[quote author="tinsn" date="1336612553"]Anyone can upload the zip file of this library ?
The download link in first page doesn't work.

BTW, switch it back to a model is a good idea.[/quote]

You can use Zebra MPTT http://stefangabos.ro/php-libraries/zebra-mptt/ and adapt it to CodeIgniter, i have done it so i know it works, but i'm not using mptt anymore.
#30

[eluser]extractortotem[/eluser]
Hi,

can somebody please paste in here a mysql schema for this library?
thanks a lot!

regards,
ET




Theme © iAndrew 2016 - Forum software by © MyBB