hierarchical-table.php

<?php
/**
 * See also {
	 @link http://dev.mysql.com/tech-resources/articles/hierarchical-data.html
	 @link http://mirror.neu.edu.cn/mysql/tech-resources/articles/hierarchical-data.html
	 @link http://www.sitepoint.com/hierarchical-data-database-2/
 * }
*/
class JTreeTable extends JObject
{
	var $table;
	/** @var DBO Data Base Object */
	var $dbo;
	var $root = 'ROOT';
	var $debug = 0;
	var $cl_pk = 'col_pk';
	var $cl_left = 'col_left';
	var $cl_right = 'col_right';
	var $cl_parent = 'col_parent';
	var $cl_title = 'col_title';
	
	function __construct( $config = array() ){
		foreach( $config as $k => $v ){
			$this->$k = $v;
		}
		$this->_init();
	}
	
	function _init(){
		$this->dbo->setQuery("SELECT ".$this->cl_pk." FROM "
			.$this->table." WHERE "
			.$this->cl_title ." = '".$this->root."'"
		);
		if(!$this->RootId = $this->dbo->loadResult()){
			try{
				$this->RootId = $this->dbo->Save($this->table, array(
					$this->cl_title => $this->root,
					$this->cl_parent => 0,
					$this->cl_left => 1,
					$this->cl_right => 2
				));
			}catch(Exception $e){
				printr($e);
				exit;
			}
		}
	}
	
	/**
	 * @logical 
	 *
	 * SELECT @myRight := rgt FROM nested_category
	 * WHERE name = 'TELEVISIONS';
	 * UPDATE nested_category SET rgt = rgt + 2 WHERE rgt > @myLeft;
	 * UPDATE nested_category SET lft = lft + 2 WHERE lft > @myLeft;
	 * INSERT INTO nested_category(name, lft, rgt) VALUES('GAME CONSOLES', @myLeft + 1, @myLeft + 2);
	 *
	 * @param {String/Int} ParentNode ID
	 * @param {Array} Assoc Array of field & values
	*/
	function addNode($parentNode, $fields = array(),$method = 'lft' ){
		$fields = (array) $fields;
		try{
			$db = $this->dbo;
			$row = $this->getNode($parentNode);
			$node_id = $fields[$this->cl_pk];
			
			/*Start Trancation*/
			$db->StartTrans();

			$update_flag = false;
			//update node
			if($node_id){
				$update_flag = true;
				$rec = $this->getNode($node_id);
				if($row->id == $rec->prt){
					//continue to save
				}else{
					$this->moveTo($rec,$row->id);
				}
			}

			if($update_flag){
				$inputsFields = $fields;
			}else{
				/*Update Right's parent*/
				$query = "UPDATE ".$this->table
					."\n SET ".$this->cl_right." = ".$this->cl_right." + 2"
					."\n WHERE ".$this->cl_right." >= ".$row->$method
				;
				$db->Execute( $query );
				
				/*Update Left's parent*/		
				$query = "UPDATE ".$this->table
					."\n SET ".$this->cl_left." = ".$this->cl_left." + 2"
					."\n WHERE ".$this->cl_left." > ".$row->$method
				;
				$db->Execute( $query );
				
				$inputsFields = @array_merge(array(
					$this->cl_parent	=> $row->id,
					$this->cl_left	=> $row->$method + (strtolower($method) == 'lft' ? 1 : 0),
					$this->cl_right	=> $row->$method + (strtolower($method) == 'lft' ? 2 : 1)
				),$fields);
			}
			
			/*save node*/
			$id = $db->Save($this->table, $inputsFields);
		
			$db->CompleteTrans();
			return $id;
		}catch(Exception $e){
			$db->RollBack();
			throw new Exception($e->getMessage());
		}
	}
	
	/**
	 * logical
	 *
	 * <code>
	 *  SELECT @myRight := rgt FROM nested_category
	 *  WHERE name = 'TELEVISIONS';
	 *  UPDATE nested_category SET rgt = rgt + 2 WHERE rgt > @myRight;
	 *  UPDATE nested_category SET lft = lft + 2 WHERE lft > @myRight;
	 *  INSERT INTO nested_category(name, lft, rgt) VALUES('GAME CONSOLES', @myRight + 1, @myRight + 2);
	 * </code>
	 *
	 * @param string|int    $parent ParentNode ID
	 * @param array         $fields Assoc Array of field & values
	 *
	 * @return int id of affected record
	*/
	function addNodeToAfter($parent, $fields = array()){
		return $this->addNode( $parent, $fields, 'rgt' );
	}
	
	/**
	 * @logical 
	 *
	 * SELECT @myLeft := lft, @myRight := rgt, @myWidth := rgt - lft + 1
	 * FROM nested_category
	 * WHERE name = 'MP3 PLAYERS';
	 * DELETE FROM nested_category WHERE lft BETWEEN @myLeft AND @myRight;
	 * UPDATE nested_category SET rgt = rgt - @myWidth WHERE rgt > @myRight;
	 * UPDATE nested_category SET lft = lft - @myWidth WHERE lft > @myRight;
	 *
	 * @param string|int $node Node ID
	 *
	 * @return boolean if success true
	 * @throws Exception if fail or not id
	*/
	function removeNode( $node ){

		if(!$node)
			throw new Exception('System.Data.TreeTable.RemoveNode.EMPTY_NODE_ID');

		$db = $this->dbo;
		$row = $this->getNode( $node );

		if(!$row) return true;
		
		$width = $row->rgt - $row->lft + 1;
				
		try{
			/*Start Trancation*/
			$db->StartTrans();
			
			$sql = "DELETE FROM ".$this->table
				."\n WHERE "
				."\n ".$this->cl_left
				."\n BETWEEN ".$row->lft." AND ".$row->rgt
				;
			$db->Execute( $sql );
	
			$sql ="UPDATE"
				."\n ".$this->table
				."\n SET " .$this->cl_right." = ".$this->cl_right." - ". $width
				."\n WHERE ".$this->cl_right." > ".$row->rgt
				;
				
			$db->Execute( $sql );
			
			$sql ="UPDATE"
				."\n ".$this->table
				."\n SET " .$this->cl_left." = ".$this->cl_left." - ". $width
				."\n  WHERE ".$this->cl_left." > ".$row->rgt
				;
				
			$db->Execute( $sql );
			
			/*Commit*/
			$db->CompleteTrans();
			return true;
		}catch(Exception $e){
			throw new Exception($e->getMessage());
		}
	}
	
	/**
	 * append existint node to new parent
	 */
	function moveTo($node, $parent_id){
		if(!is_object($node)) $node = $this->getNode($node);

		//if not chante
		if($parent_id == $node->prt) return true;
		
		$db = $this->dbo;
		
		try{
			$db->StartTrans();
			
			//reset l&r
			$width = $node->rgt - $node->lft + 1;
			$sql ="UPDATE"
				."\n ".$this->table
				."\n SET " .$this->cl_right." = ".$this->cl_right." - ". $width
				."\n WHERE ".$this->cl_right." > ".$node->rgt
			;
			$db->Execute($sql);
			
			$sql ="UPDATE"
				."\n ".$this->table
				."\n SET " .$this->cl_left." = ".$this->cl_left." - ". $width
				."\n  WHERE ".$this->cl_left." > ".$node->rgt
			;
			$db->Execute($sql);
			
			//get new parent after reset
			$pt = $this->getNode($parent_id);
			
			/*Update Right's parent*/
			$sql = "UPDATE ".$this->table
				."\n SET ".$this->cl_right." = ".$this->cl_right." + 2"
				."\n WHERE ".$this->cl_right." >= ".$pt->rgt
				;
			$db->Execute($sql);
			
			/*Update Left's parent*/		
			$sql = "UPDATE ".$this->table
				."\n SET ".$this->cl_left." = ".$this->cl_left." + 2"
				."\n WHERE ".$this->cl_left." > ".$pt->rgt
			;
			$db->Execute($sql);
			
			//save to new parent
			$db->Save($this->table,array(
				$this->cl_pk 		=> $node->id,
				$this->cl_parent 	=> $pt->id,
				$this->cl_left 		=> $pt->rgt,
				$this->cl_right 	=> $pt->rgt + 1
			));

			$db->CompleteTrans();
			return true;
		}catch(Exception $e){
			throw new Exception($e->getMessage());
			return false;
		}
	}
	
	function moveToAfter($node, $target_id){
		$db = $this->dbo;
		
		try{
			$db->StartTrans();
			
			//move if diff parent
			$tg = $this->getNode($target_id);
			$this->moveTo($node, $tg->prt);
			if(!is_object($node)) $node = $this->getNode($node);
			$pt = $this->getNode($tg->prt);
			
			$sql = "UPDATE ".$this->table
				."\n SET"
				."\n ".$this->cl_left." = ".$this->cl_left." + 2"
				."\n ,".$this->cl_right." = ".$this->cl_right." + 2"
				."\n WHERE ".$this->cl_left." > ".$tg->rgt." AND ".$this->cl_right." < ".$pt->rgt
			;
			$db->Execute($sql);
			
			//save to new pos
			$db->Save($this->table,array(
				$this->cl_pk 		=> $node->id,
				$this->cl_left 		=> $tg->rgt,
				$this->cl_right 	=> $tg->rgt + 1
			));
			
			$db->CompleteTrans();
			return true;
		}catch(Exception $e){
			throw new Exception($e->getMessage());
			return false;
		}
	}
	
	function moveToBefor($node, $target_id){
		$db = $this->dbo;
		
		try{
			$db->StartTrans();
			
			//move if diff parent
			$tg = $this->getNode($target_id);
			$this->moveTo($node, $tg->prt);
			if(!is_object($node)) $node = $this->getNode($node);
			$pt = $this->getNode($tg->prt);
			
			$sql = "UPDATE ".$this->table
				."\n SET"
				."\n ".$this->cl_left." = ".$this->cl_left." + 2"
				."\n ,".$this->cl_right." = ".$this->cl_right." + 2"
				."\n WHERE ".$this->cl_left." >= ".$tg->lft." AND ".$this->cl_right." < ".$pt->rgt
			;
			$db->Execute($sql);

			//save to new pos
			$db->Save($this->table,array(
				$this->cl_pk 		=> $node->id,
				$this->cl_left 		=> $tg->lft,
				$this->cl_right 	=> $tg->rgt
			));
			
			$db->CompleteTrans();
			return true;
		}catch(Exception $e){
			throw new Exception($e->getMessage());
			return false;
		}
	}
	
	/**
	 * Move node up/down under self node
	*/
	function moveOrder($node,$type){
		$n = $this->getNode( $node );
		if(!$n) return;
		
		try{
			if(strtolower($type) == 'up'){
				$pn = $this->getNodeWhere(array(
					$this->cl_right." = ".$n->lft-1
				));
			}else{
				$pn = $this->getNodeWhere(array(
					$this->cl_left." = ".$n->rgt+1
				));
			}
			
			/*Start Trancation*/
			$this->dbo->StartTrans();
			
			$this->dbo->Execute( "UPDATE ".$this->table
				."\n SET ".$this->cl_left." = ".$n->lft.","
				.$this->cl_right." = ".$n->rgt
				."\n WHERE ".$this->cl_pk." = ".$pn->id );
				
			$this->dbo->Execute( "UPDATE ".$this->table
				."\n SET ".$this->cl_left." = ".$pn->lft.","
				.$this->cl_right." = ".$pn->rgt
				."\n WHERE ".$this->cl_pk." = ".$n->id );
				
			/*Commit*/
			$this->dbo->CompleteTrans();
			return true;
		}catch(Exception $e){
			$this->_error = $e->getMessage();
			throw new Exception($e->getMessage());
			return false;
		}
	}
	
	/**
	 * Move node up under self node
	*/
	function moveUp($node){
		return $this->moveOrder( $node, 'up' );
	}
	
	/**
	 * Move node down under self node
	*/
	function moveDown($node){
		return $this->moveOrder( $node, 'down' );
	}
	
	/**
	 * @get node
	 * @param $node node id
	*/
	function getNode($node_id){
		$db = $this->dbo;
		$sql = "SELECT *,".$this->cl_pk." AS id,"
			."\n ".$this->cl_parent." AS prt,"
			."\n ".$this->cl_left." AS lft,"
			."\n ".$this->cl_right." AS rgt"
			."\n FROM ".$this->table
			."\n WHERE ".$this->cl_pk." = ".$node_id
		;
		$db->setQuery($sql);
		return $db->getRecord();
	}
	
	function getNodeWhere( $where ){
		$db = $this->dbo;
		$sql = "SELECT *,".$this->cl_pk." AS id,"
			."\n ".$this->cl_parent." AS prt,"
			."\n ".$this->cl_left." AS lft,"
			."\n ".$this->cl_right." AS rgt"
			."\n FROM ".$this->table
			."\n WHERE ".implode( ' AND ', $where );
		;
		$db->setQuery($sql);
		return $db->getRecord();
	}
	
	function getParent( $node ){
		$row = $this->getNode( $node );
		return $this->getNode( $row->prt );
	}
	
	function _buildQuery($root = false){
			$sqlLevel = "SELECT"
			. "\n g1.".$this->cl_pk." AS ID,g1.*,"
			. "\n COUNT(g2.".$this->cl_pk.")".($root ? '' : "-1")." AS Level,"
			. "\n ROUND((g1.".$this->cl_right."-g1.".$this->cl_left.")/2) AS hasChild"
			. "\n FROM ".$this->table." AS g1"
			. "\n INNER JOIN ".$this->table." AS g2 ON g1."
			. $this->cl_left." BETWEEN g2."
			. $this->cl_left." AND g2.".$this->cl_right
			. ($root ? "" : "\n WHERE g1.".$this->cl_parent.' != 0')
		 	. "\n GROUP BY g1.".$this->cl_pk
			. "\n ORDER BY g1.".$this->cl_left
		;
		return $sqlLevel;
	}

	function getTreeLevel($level = 10,$root = false){
		$this->dbo->setQuery("SELECT * FROM(" . $this->_buildQuery($root) . ") AS TREE WHERE Level <= " . $level );
		$rows = array();
		$rows = $this->dbo->loadObjectList();
		return $rows;
	}

	function getTreeWhere($where = array(),$root = false){
		$this->dbo->setQuery("SELECT * FROM(" . $this->_buildQuery($root) . ") AS TREE WHERE " . implode("\nAND\n",$where) );
		$rows = array();
		$rows = $this->dbo->loadObjectList();
		return $rows;
	}

	function getTree($root = false){
		$this->dbo->setQuery( $this->_buildQuery($root) );
		$rows = array();
		$rows = $this->dbo->loadObjectList();
		return $rows;
	}

	function getAllChilds($node_id,$root = true){
		$node = $this->getNode($node_id);
		$rows = $this->dbo->setQuery("SELECT * FROM %s WHERE %s BETWEEN %s AND %s ORDER BY %s ASC",array(
			$this->table,
			$this->cl_left,
			$node->lft,
			$node->rgt,
			$this->cl_left
		))
			->loadObjectList()
		;
		if($root) return $rows;
		unset($rows[0]);
		return $rows;
	}

	function getAllChildIds($node_id,$root = true){
		$node = $this->getNode($node_id);
		$rows = $this->dbo->setQuery("SELECT %s FROM %s WHERE %s BETWEEN %s AND %s ORDER BY %s ASC",array(
			$this->cl_pk,
			$this->table,
			$this->cl_left,
			$node->lft,
			$node->rgt,
			$this->cl_left
		))
			->loadResultArray()
		;
		if($root) return $rows;
		unset($rows[0]);
		return $rows;
	}

	function RebuildNode($n){
		$node = $this->getNode($n);
		if(!$node) return;
		return $this->Rebuild($node->prt,$node->lft);
	}
	
	function Rebuild($parent_id=0, $left=0){
		$this->dbo->StartTrans();
		$this->rebuildTree($parent_id, $left);
		$this->dbo->CompleteTrans();
		if($this->dbo->getErrorNum()) {
			$this->_error = $this->dbo->getErrorMsg();
			return false;
		}else{
			return true;
		}
	}
	
	function rebuildTree($parent_id=0, $left=0){
		$right = $left+1; 

		$this->dbo->setQuery("SELECT ".$this->cl_pk." AS id FROM ". $this->table
			."\n WHERE ".$this->cl_parent." = ".$parent_id
		); 
	   $rows = (array) $this->dbo->loadObjectlist();
	
		foreach( $rows as $row ) {
			$right = $this->rebuildTree( $row->id, $right ); 
		}
		
	   $sql = "UPDATE ".$this->table
			."\n SET ".$this->cl_left." = ".$left
			."\n ,".$this->cl_right." = ". $right
			."\n WHERE "
			. $this->cl_pk." = '".$parent_id."'"
		; 
		$this->dbo->Execute( $sql );
		return $right+1; 
	}
}
?>