Here's what I did ... /** save as tree_model.php in cake root **/
<?php /** * TreeMedel class deployment to work with nested sets data in database. * It cakephp's native model class witch can be inherited in your application. * @author Samoshin Ivan aka azzis * @website http://my.kht.ru * @version 0.1.0 * @date 2007-03-01 9:49 * @license MIT */ /** * @package TreeModel class */ class TreeModel extends AppModel { /** * The name of the model * * @var string * @access public */ var $name = 'TreeModel'; /** * Right field name in the table * * @var string * @access public */ var $right = 'rgt'; /** * Left field name in the table * * @var string * @access public */ var $left = 'lft'; /** * Error string for some functions * * @var string * @access public */ var $error; /** * Reset database function * * @param boolean $force - force clear data in tabel * @param string $rootname - name of the root node * @return error string if not successful */ function cleanDB($force = false, $rootname = 'root') { if (!$force) { if ($this->findCount() > 0) $continue = false; $this->error = "Your {$this->table} table is not empty, and you didn't request a forced clean!\n"; return $this->error; } if ($force || $continue) { $this->query("delete from {$this->table}"); $this->query("insert into {$this->table} (name,{$this->left}, {$this->right},id) values ('{$rootname}',1,2,1)"); $this->query("set insert_id=2"); } if (mysql_errno() > 0) { $this->error = "Database error: ".mysql_error()."\n"; return $this->error; } } /** * Get full tree from database function * * @return array of rows from model's table */ function enumTree() { $data = $this->query("SELECT node.*, (count(parent.name)-1) as depth, IF((node.{$this->right} = node.{$this- >left}+1), true, null) as is_leaf, ( SELECT IF (n.id, true, null) FROM {$this->table} as n WHERE n.{$this->left}=node.{$this- >left}-1 ) as is_first, ( SELECT IF (n.id, true, null) FROM {$this->table} as n WHERE n.{$this->right}=node.{$this- >right}+1 ) as is_last FROM {$this->table} as node, {$this->table} as parent WHERE node.{$this->left} BETWEEN parent. {$this->left} AND parent.{$this->right} GROUP BY node.id ORDER BY node.{$this->left} ASC"); return $this->_reduce($data); } /** * @desc return root nodes of tree */ function enumRoots() { $tree = $this->enumTree(); $roots = array(); foreach ($tree as $node) { if ($node[0]['depth'] == 0) $roots[] = $node; } return $roots; } /** * Get node info * * @param integer $id - node identifier * @return array of field values (id, name, lft, rgt, depth, is_leaf, is_first, is_last) */ function enumNode($id=1) { $data = $this->query("SELECT node.*, (count(parent.name)-1) as depth, IF((node.{$this->right} = node.{$this- >left}+1), true, null) as is_leaf, ( SELECT IF (n.id, true, null) FROM {$this->table} as n WHERE n.{$this->left}=node.{$this- >left}-1 ) as is_first, ( SELECT IF (n.id, true, null) FROM {$this->table} as n WHERE n.{$this->right}=node.{$this- >right}+1 ) as is_last FROM {$this->table} as node, {$this->table} as parent WHERE node.{$this->left} BETWEEN parent. {$this->left} AND parent.{$this->right} AND node.id = {$id} GROUP BY node.id"); if (empty($data)) { $this->error = "Node with id = {$id} not found in tree!"; return $this->error; } $data = $this->_reduce($data); return $data[0]; } /** * Get only leaf nodes function * * @return array of rows */ function enumLeafNodes() { $data = $this->query("SELECT node.*, (count(parent.name)-1) as depth, IF((node.{$this->right} = node.{$this- >left}+1), true, null) as is_leaf, ( SELECT IF (n.id, true, null) FROM {$this->table} as n WHERE n.{$this->left}=node.{$this- >left}-1 ) as is_first, ( SELECT IF (n.id, true, null) FROM {$this->table} as n WHERE n.{$this->right}=node.{$this- >right}+1 ) as is_last FROM {$this->table} as node, {$this->table} as parent WHERE node.{$this->left} BETWEEN parent. {$this->left} AND parent.{$this->right} AND node.{$this->right} = node.{$this- >left} + 1 GROUP BY node.id ORDER BY node.{$this->left} ASC"); return $this->_reduce($data); } /** * Get full path to the node function * * @param integer $id - node identifier * @param boolean $include_node - self node presence in path * @return array of rows */ function enumPath($id, $include_node=false) { $inc = ($include_node) ? 0 : 1; $data = $this->query("SELECT node.*, (count(parent.name)-1) as depth, IF((node.{$this->right} = node.{$this- >left}+1), true, null) as is_leaf, ( SELECT IF (n.id, true, null) FROM {$this->table} as n WHERE n.{$this->left}=node.{$this- >left}-1 ) as is_first, ( SELECT IF (n.id, true, null) FROM {$this->table} as n WHERE n.{$this->right}=node.{$this- >right}+1 ) as is_last FROM {$this->table} as node, {$this->table} as parent WHERE node.{$this->left} BETWEEN parent. {$this->left} AND parent.{$this->right} AND node.id in ( SELECT parent.id FROM {$this->table} as node, {$this- >table} as parent WHERE node.{$this->left} BETWEEN (parent.{$this->left}+{$inc}) AND (parent.{$this->right}-{$inc}) AND node.id = {$id} ) GROUP BY node.id ORDER BY node.{$this->left} ASC"); return $this->_reduce($data); } /** * Get parents of node * * @param integer $id - node identifier * @param integer $count - count of the parents from node * @return array of rows */ function enumParents($id, $count=null) { if ($count === null) { $limit = ''; } else { $limit = " LIMIT {$count}"; } $data = $this->query("SELECT parent.*, (count(parent.name)-1) as depth, IF((node.{$this->right} = node.{$this- >left}+1), true, null) as is_leaf, ( SELECT IF (n.id, true, null) FROM {$this->table} as n WHERE n.{$this->left}=node.{$this- >left}-1 ) as is_first, ( SELECT IF (n.id, true, null) FROM {$this->table} as n WHERE n.{$this->right}=node.{$this- >right}+1 ) as is_last FROM structure_units as node, structure_units as parent, structure_units as parent_ WHERE node.id = {$id} AND parent.{$this- >left} < node.{$this->left} AND parent.{$this->right} > node.{$this- >right} AND parent.{$this->left} BETWEEN parent_. {$this->left} AND parent_.{$this->right} GROUP BY parent.id ORDER BY parent.{$this->left} DESC ". $limit); return $this->_reduce($data); } /** * Get tree exclude node and his descendants * * @param integer $id - node identifier * @return array of rows */ function enumTreeExcludeNode($id) { $data = $this->query("SELECT node.*, (count(parent.name)-1) as depth, IF((node.{$this->right} = node.{$this- >left}+1), true, null) as is_leaf, ( SELECT IF (n.id, true, null) FROM {$this->table} as n WHERE n.{$this->left}=node.{$this- >left}-1 ) as is_first, ( SELECT IF (n.id, true, null) FROM {$this->table} as n WHERE n.{$this->right}=node.{$this- >right}+1 ) as is_last FROM {$this->table} as node, {$this->table} as parent, {$this->table} as n WHERE node.{$this->left} BETWEEN parent. {$this->left} AND parent.{$this->right} AND n.id={$id} AND node.{$this->left} not BETWEEN n.{$this->left} AND n.{$this->right} GROUP BY node.id ORDER BY node.{$this->left} ASC"); return $this->_reduce($data); } /** * Get sub tree * * @param integer $id - node identifier * @return array of rows */ function enumSubTree($id) { $data = $this->query("SELECT node.*, (count(parent.name)-1) as depth, IF((node.{$this->right} = node. {$this->left}+1), true, null) as is_leaf, ( SELECT IF (n.id, true, null) FROM {$this->table} as n WHERE n.{$this->left}=node.{$this- >left}-1 ) as is_first, ( SELECT IF (n.id, true, null) FROM {$this->table} as n WHERE n.{$this->right}=node.{$this- >right}+1 ) as is_last FROM {$this->table} as node, {$this->table} as parent, {$this->table} as n WHERE node.{$this->left} BETWEEN parent. {$this->left} AND parent.{$this->right} AND n.id = {$id} AND node.{$this->left} BETWEEN n.{$this- >left} AND n.{$this->right} GROUP BY node.id ORDER BY node.{$this->left} ASC"); return $this->_reduce($data); } /** * Get all descendants of the node * * @param integer $id - node identifier * @param boolean $include_parent - include parent node * @return array of rows */ function enumChildren($id, $include_parent=false) { $compare = (!$include_parent) ? '=' : '<='; $data = $this->query("SELECT node.*, (count(parent.name)-1) as depth, IF((node.{$this->right} = node.{$this- >left}+1), true, null) as is_leaf, ( SELECT IF (n.id, true, null) FROM {$this->table} as n WHERE n.{$this->left}=node.{$this- >left}-1 ) as is_first, ( SELECT IF (n.id, true, null) FROM {$this->table} as n WHERE n.{$this->right}=node.{$this- >right}+1 ) as is_last FROM {$this->table} as node, {$this->table} as parent, {$this->table} as n WHERE node.{$this->left} BETWEEN parent. {$this->left} AND parent.{$this->right} AND n.id = {$id} AND node.{$this->left} BETWEEN n.{$this- >left} AND n.{$this->right} GROUP BY node.id HAVING depth-1 {$compare} ( SELECT (COUNT(parent.name) - 1) AS depth FROM {$this->table} AS node, {$this->table} AS parent WHERE node.{$this->left} BETWEEN parent. {$this->left} AND parent.{$this->right} AND node.id = {$id} GROUP BY node.id ORDER BY node.{$this->left} ) ORDER BY node.{$this->left} ASC"); return $this->_reduce($data); } /** * Insert node below * * @param integer $id - node below identifier * @param string $name - name of inserted node * @param integer $useraction_id - foreign key */ function insertBelow($name,$useraction_id,$id=0) { $this->query("LOCK TABLE {$this->table} WRITE"); if ($id != 0) { $rgt = $this->query("SELECT {$this->right} FROM {$this->table} WHERE id={$id}"); $rgt = $rgt[0][$this->table][$this->right]; $this->query("UPDATE {$this->table} SET {$this->right}={$this->right}+2 WHERE {$this->right} > {$rgt}"); $this->query("UPDATE {$this->table} SET {$this->left}={$this->left}+2 WHERE {$this->left} > {$rgt}"); } else { $rgt = 0; } $this->query("INSERT INTO {$this->table} (name,useraction_id, lft, rgt) VALUES ('{$name}','{$useraction_id}', {$rgt} + 1, {$rgt} + 2)"); $this->query("UNLOCK TABLES;"); } /** * @desc insert new root node */ function insertRoot($name,$useraction_id) { $maxrgt = $this->query("SELECT id FROM {$this->table} ORDER BY rgt DESC LIMIT 1"); if ($this->getNumRows() > 0) { $maxrgt = $maxrgt[0][$this->table]['id']; } else { $maxrgt = 0; } // print_r($maxrgt); $this->insertBelow($name,$useraction_id,$maxrgt); } /** * Insert node into * * @param integer $id - node into identifier * @param string $name - name of inserted node */ function insertInto($name,$useraction_id, $id) { $this->query("LOCK TABLE {$this->table} WRITE"); $rgt = $this->query("SELECT {$this->right} FROM {$this->table} WHERE id={$id}"); $rgt = $rgt[0][$this->table][$this->right]; $this->query("UPDATE {$this->table} SET {$this->left}={$this->left}+2 WHERE {$this->left} > {$rgt} ORDER BY lft DESC"); $this->query("UPDATE {$this->table} SET {$this->right}={$this->right}+2 WHERE {$this->right} >= {$rgt} ORDER BY rgt DESC"); $this->query("INSERT INTO {$this->table} (name, useraction_id, {$this->left}, {$this->right}) VALUES ('{$name}','{$useraction_id}', {$rgt}, {$rgt} + 1)"); $this->query("UNLOCK TABLES;"); } /** * Delete node without of descentants * * @param integer $id - deleted node */ function deleteLeaf($id) { $this->query("LOCK TABLE {$this->table} WRITE"); $result = $this->query("SELECT {$this->left}, {$this->right}, ({$this->right}-{$this->left}+1) as width FROM {$this->table} WHERE id={$id}"); if (empty($result)) { $this->query('UNLOCK TABLES'); $this->error = "Node with id=$id not found in table ".$this- >table."!"; return $this->error; } $result = $this->_reduce($result); $lft = $result[0][$this->table][$this->left]; $rgt = $result[0][$this->table][$this->right]; $this->query("DELETE FROM {$this->table} WHERE {$this- >left}={$lft}"); $this->query("UPDATE {$this->table} SET {$this->right} = {$this- >right} - 1, {$this->left} = {$this->left} - 1 WHERE {$this->left} BETWEEN {$lft} AND {$rgt}"); $this->query("UPDATE {$this->table} SET {$this->left}={$this->left}-2 WHERE {$this->left} > {$rgt} ORDER BY lft DESC"); $this->query("UPDATE {$this->table} SET {$this->right}={$this->right}-2 WHERE {$this->right} >= {$rgt} ORDER BY rgt DESC"); $this->query('UNLOCK TABLES'); } /** * Delete node with all descentants * * @param integer $id - deleted node */ function deleteNode($id) { $this->query("LOCK TABLE {$this->table} WRITE"); $result = $this->query("SELECT {$this->left}, {$this->right}, ({$this->right}-{$this->left}+1) AS width FROM {$this->table} WHERE id={$id}"); if (empty($result)) { $this->query('UNLOCK TABLES'); $this->error = "Node with id=$id not found in table ".$this- >table."!"; return $this->error; } $result = $this->_reduce($result); $lft = $result[0][$this->table][$this->left]; $rgt = $result[0][$this->table][$this->right]; $width = $result[0][0]['width']; $this->query("DELETE FROM {$this->table} WHERE {$this->left} BETWEEN {$lft} AND {$rgt}"); $this->query("UPDATE {$this->table} SET {$this->right}={$this- >right}-{$width} WHERE {$this->right} > {$rgt}"); $this->query("UPDATE {$this->table} SET {$this->left}={$this- >left}-{$width} WHERE {$this->left} > {$rgt}"); $this->query("UNLOCK TABLES"); } /** * Move source node with descendants after destination * * @param integer $src_id - moved node * @param integer $dst_id - destination node * @return new left and right positions for the moved node */ function moveNodeAfter($src_id, $dst_id) { $src = $this->enumNode($src_id); $dst = $this->enumNode($dst_id); return $this->_moveSubtree($src, $dst['rgt']+1); } /** * Move source node with descendants before destination * * @param integer $src_id - moved node * @param integer $dst_id - destination node * @return new left and right positions for the moved node */ function moveNodeBefore($src_id, $dst_id) { $src = $this->enumNode($src_id); $dst = $this->enumNode($dst_id); return $this->_moveSubtree($src, $dst['lft']); } /** * Move source node with descendants to first position into destination * * @param integer $src_id - moved node * @param integer $dst_id - destination node * @return new left and right positions for the moved node */ function moveToFirstChild($src_id, $dst_id) { $src = $this->enumNode($src_id); $dst = $this->enumNode($dst_id); return $this->_moveSubtree($src, $dst['lft']+1); } /** * Move source node with descendants to last position into destination * * @param integer $src_id - moved node * @param integer $dst_id - destination node * @return new left and right positions for the moved node */ function moveToLastChild($src_id, $dst_id) { $src = $this->enumNode($src_id); $dst = $this->enumNode($dst_id); return $this->_moveSubtree($src, $dst['rgt']); } /** * Private helper method to merge array elements * * @access protected * @param integer $data - array of rows from database * @return merged data array */ function _reduce($data) { foreach ($data as $item) { $item = array_reduce($item, array('TreeModel','__reduce_callback')); } return $data; } function __reduce_callback ($res_arr, $arr) { if (!is_array($res_arr)) $res_arr = array(); $res_arr = array_merge($res_arr, $arr); return $res_arr; } /** * Private helper method to move node * * @access protected * @param array $src - node row from database * @param integer $to * @return new left and right positions for the moved node */ function _moveSubtree ($src, $to) { if (empty($src) || empty($to)) { $this->error = "Move node id = {$src['id']} is missing!"; return $this->error; } $treesize = $src[$this->right] - $src[$this->left] + 1; $this->query("UPDATE {$this->table} SET {$this->left} = {$this- >left} + {$treesize} WHERE {$this->left} >= {$to}"); $this->query("UPDATE {$this->table} SET {$this->right} = {$this- >right} + {$treesize} WHERE {$this->right} >= {$to}"); if($src[$this->left] >= $to){ $src[$this->left] += $treesize; $src[$this->right] += $treesize; } $this->query("UPDATE {$this->table} SET {$this->left} = {$this- >left} + ".($to - $src[$this->left])." WHERE {$this->left} >= ". $src[$this->left]." AND {$this->left} <= ".$src[$this->right]); $this->query("UPDATE {$this->table} SET {$this->right} = {$this- >right} + ".($to - $src[$this->left])." WHERE {$this->right} >= ". $src[$this->left]." AND {$this->right} <= ".$src[$this->right]); $newpos = array($this->left => $src[$this->left] + $to - $src[$this->left], $this->right => $src[$this->right] + $to - $src[$this->left]); $this->query("UPDATE {$this->table} SET {$this->left} = {$this- >left} + ".(-$treesize)." WHERE {$this->left} >= ".($src[$this->right] + 1)); $this->query("UPDATE {$this->table} SET {$this->right} = {$this- >right} + ".(-$treesize)." WHERE {$this->right} >= ".($src[$this- >right] + 1)); if ($src[$this->left] <= $to) { $newpos[$this->left] -= $treesize; $newpos[$this->right] -= $treesize; } return $newpos; } } ?> In the model add require_once('tree_model.php'); before the class definition and you are ready to go ... ie function append($id,$root) { if(!empty($this->data)) { $this->cleanUpFields(); $name = $this->Pipeline->read(0,$id); $this->Pipeline->insertInto($name['Pipeline']['name'], $this->data['Pipeline']['useraction_id'],$id); $this->Session->setFlash('The Pipeline has been appended'); $this->redirect(array('action'=>'view/'.$root), null, true); } $useractions = $this->Pipeline->Useraction->generateList(); $this->set(compact('useractions')); $this->set('id',$id); $this->set('root',$root); } This requires a table layout like: CREATE TABLE `pipelines` ( `id` int(10) unsigned NOT NULL auto_increment, `name` varchar(40) collate utf8_unicode_ci NOT NULL, `useraction_id` int(10) unsigned NOT NULL, `lft` int(10) unsigned NOT NULL, `rgt` int(10) unsigned NOT NULL, PRIMARY KEY (`id`), KEY `useraction_id` (`useraction_id`), KEY `lft` (`lft`), KEY `rgt` (`rgt`) ) Some good information can be found here http://www.sitepoint.com/article/hierarchical-data-database. I recall that some of the methods in the tree class still needed some tweaking ... but the main one (appends, delete etc) would work correctly Hope this helps ... On 27 Nov., 10:23, CakeMan <[EMAIL PROTECTED]> wrote: > Hi Guys, > > I am looking for working with Tree behaviour however i am not finding > any > Tutorials or article for it. I am having cake1.2 pre beta. > > pls help. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Cake PHP" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cake-php?hl=en -~----------~----~----~----~------~----~------~--~---
