CODEWITHSUNDEEP
phpyii
Malkocoglu
Malkocoglu

Reputation: 477

Generating tree from database in Yii

I have a model in Yii which represents a tree, with the following MySQL table:

CREATE TABLE IF NOT EXISTS `nodes` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `node` varchar(255) NOT NULL,
    [ something more not necessary to display here ]
    `parentid` int(11) NOT NULL DEFAULT '0',
    PRIMARY KEY (`id`),
) ENGINE=InnoDB;

I have the following relations for this model:

return array(
    'parentnode'=>array(self::BELONGS_TO, 'Nodes', 'parentid'),
    'childnode'=>array(self::HAS_MANY, 'Nodes', 'parentid'),
);

I am using CTreeView to display the tree. Building the source array with iterating all nodes with parentid=0 recursively using $model->childnode. This is the iterating function:

public static function nodetree($params) { //finds all top-level nodes
    $retval=array();
    $nodes=Nodes::model()->findAllByAttributes(array('parentid'=>0));
    foreach($nodes as $anode)
        $retval[]=Nodes::nodearray($anode, $params);
}

This is the recursing function.

public static function nodearray($_node, $params) { // finds children
    $retval=array(
        'text'=>$_node->node, //may differ based on options
        'id'=>$_node->id,
        'expanded'=>false,    //may differ based on options
        'children'=>array(),
    );
    foreach ($_node->childnode as $c_node)
        $retval['children'][]=Nodes::nodearray($c_node, $params);
    return $retval;
}

Probably this is not the fastest approach because of Yii's overhead. Page generation takes more than 1 second on a development server with no other application running. Nodes are more than 1K and are being updated by users when required.

How can I generate the tree/page faster?

Upvotes: 4

Views: 1990

Answers (2)

Malkocoglu
Malkocoglu

Reputation: 477

This is the most efficient method as I tested upto now. Derived from http://blog.ideashower.com/post/15147134343/create-a-parent-child-array-structure-in-one-pass

public static function nodetree($param=array()) {
    $refs = array();
    $list = array();

    $nodes = Yii::app()->db->createCommand('select * from nodes')->queryAll();

    foreach ($nodes as $data) {
        $thisref = &$refs[ $data['id'] ];
        $thisref['parentid'] = $data['parentid'];
        $thisref['text'] = $data['node'];
        if ($data['parentid'] == 0) {
            $list[ $data['id'] ] = &$thisref;
        } else {
            $refs[ $data['parentid'] ]['children'][ $data['id'] ] = &$thisref;
        }   
    }           
    return $list;
}

Upvotes: 2

Malkocoglu
Malkocoglu

Reputation: 477

Well, it is a shame that I answer my own question, but I found a solution, somehow.

I created a caching table:

CREATE TABLE IF NOT EXISTS `treecache` (
    `param` varchar(255) NOT NULL,
    `cachedtree` mediumtext NOT NULL,
    PRIMARY KEY (`param`)
) ENGINE=InnoDB;

I changed nodetree function as follows:

public static function nodetree($param) { //finds all top-level nodes
    $paramstr = serialize($param);
    if (strlen($paramstr)<256) {
        $oval=Yii::app()->db->createCommand('SELECT cachedtree FROM treecache WHERE param=:param')->queryRow(true, array('param'=>$paramstr));
        if (isset($oval['cachedtree'])) return unserialize($oval['cachedtree']);                    
    }
    $retval=array();
    $nodes=Nodes::model()->findAllByAttributes(array('parentid'=>0));
    foreach($nodes as $anode)
        $retval[]=Nodes::nodearray($anode, $param);
    if (strlen($paramstr)<256)
        Yii::app()->db->createCommand('INSERT INTO treecache VALUES (:param, :cachedtree)')->execute(array('param'=>$paramstr, 'cachedtree'=>serialize($retval)));
    return $retval;
}

Furtermore, in order to cope with node changes, I added a new function to my model:

public function afterSave() {
    Yii::app()->db->createCommand('TRUNCATE treecache')->execute();
    return parent::afterSave();
}

This does not speed tree generation process but caches generated trees based on parameters and returns the result much quicker than iterating/recursing. I am still open to any comments...

Upvotes: 2

Related Questions