Location: PHPKode > scripts > Nested Set DB Table > libs/ZFversion/NestedSetDbTable/Abstract.php
<?php
/**
 * Abstract class that extends capabilities of Zend_Db_Table 
 * class, providing API for managing some Nested set table in 
 * database.
 *
 * @author Nikola Posa <hide@address.com>
 * @license http://opensource.org/licenses/gpl-3.0.html GNU General Public License
 */
abstract class NestedSetDbTable_Abstract extends Zend_Db_Table
{
	const FIRST_CHILD  = 'firstChild';
	const LAST_CHILD   = 'lastChild';
	const NEXT_SIBLING = 'nextSibling';
	const PREV_SIBLING = 'prevSibling';
	
    /**
     * Left column name in nested table.
     *
     * @var string
     */
    protected $_left;
 
    /**
     * Right column name in nested table.
     *
     * @var string
     */
    protected $_right;
	
	/**
     * Additional SQL WHERE clause to be used in process of 
	 * determining  left and right column values, getting 
	 * whole tree, etc.
     *
     * @var string
     */
    protected $addWhere = null;
	
	/**
     * Constructor.
     *
	 * Supported params for $config are:
     *   left     - left column name,
     *   right    - right column name,
	 *   addWhere - additional SQL WHERE clause.
	 *
	 * @param array An array of user-specified config options.
     * @return Zend_Db_Table
     */
	public function __construct($config = array())
    {
		if (!empty($config)) {
            $this->setNestedOptions($config);
        }
		
		if (!$this->_left || !$this->_right) {
			include_once('NestedSetDbTable/Exception.php');
			throw new NestedSetDbTable_Exception('Both "left" and "right" column names must be supplied.');
		}
		
        parent::__construct($config);
		
		$this->_setupPrimaryKey();
		
		$this->prepareAddWhere();
    }
	
	/**
	 * Sets config options.
	 *
	 * @param array Config options.
	 * @return void
	 */
	public function setNestedOptions($options)
	{
		foreach ($options as $key => $value) {
			switch ($key) {
                case 'left':
                    $this->_left = (string)$value;
                    break;
                case 'right':
                    $this->_right = (string)$value;
                    break;
				case 'addWhere':
                    $this->setAddWhere($value);
                    break;
                default:
                    break;
            }
		}
	}
	
	/**
	 * Prepares additional WHERE clause.
	 *
	 * @return void
	 */
	protected function prepareAddWhere()
	{
		if ($this->addWhere != null) {
			$this->addWhere = (string)$this->addWhere;
			
			$this->addWhere = preg_replace('/^where/i', '', trim($this->addWhere));
		}
		else {
			$this->addWhere = '';
		}
	}

	/**
	 * Sets additional WHERE clause.
	 *
	 * @param string Additional SQL WHERE clause.
	 * @return void
	 */
	public function setAddWhere($addWhere)
	{
		$this->addWhere = $addWhere;
		
		$this->prepareAddWhere();
	}
	
	/**
	 * Checks whether valid node position is supplied.
	 *
	 * @param string Position regarding on objective node.
	 * @return bool
	 */
	private function checkNodePosition($position)
	{
		$r = new ReflectionClass($this);
		
		if (!in_array($position, $r->getConstants())) {
			return false;
		}
        
		return true;
	}
	
	/**
	 * Gets whole tree, including depth information.
	 *
	 * @return array
	 */
	public function getTree()
	{
		$tableName = $this->getAdapter()->quoteIdentifier($this->_name);
		$primary = $this->getAdapter()->quoteIdentifier($this->_primary[1]);
		$leftCol = $this->getAdapter()->quoteIdentifier($this->_left);
		$rightCol = $this->getAdapter()->quoteIdentifier($this->_right);
		
		$addWhere = '';
		if (strlen($this->addWhere) > 0) {
			$addWhere = ' AND ' . $this->addWhere;
		}
			
		$sql = "SELECT node.*, (COUNT(parent.$primary) - 1) AS 'depth'
		FROM $tableName node, $tableName parent
		WHERE node.$leftCol BETWEEN parent.$leftCol AND parent.$rightCol $addWhere
		GROUP BY node.$primary
		ORDER BY node.$leftCol";
		
		return $this->getAdapter()->fetchAll($sql);
	}
	
	/**
     * Function for adding new node.
     *
	 * @param array Submitted data.
	 * @param int|null Objective node id.
	 * @param string Position regarding on objective node.
	 * @return mixed
     */
	public function insertNode($data, $objectiveNodeId = null, $position = self::LAST_CHILD)
	{
		if (!$this->checkNodePosition($position)) {
			include_once('NestedSetDbTable/Exception.php');
			throw new NestedSetDbTable_Exception('Invalid node position is supplied.');
		}
		
		$data = array_merge($data, $this->getLftRgt($objectiveNodeId, $position));
		
		return $this->insert($data);
	}
	
	/**
     * Updates info of some node.
     *
	 * @param array Submitted data.
	 * @param int Id of a node that is being updated.
	 * @param int Objective node id.
	 * @param string Position regarding on objective node.
	 * @return mixed
     */
	public function updateNode($data, $id, $objectiveNodeId, $position)
	{
		$id = (int)$id;
		
		if (!$this->checkNodePosition($position)) {
			include_once('NestedSetDbTable/Exception.php');
			throw new NestedSetDbTable_Exception('Invalid node position is supplied.');
		}
		
		if ($objectiveNodeId != $this->getCurrentObjectiveId($id, $position)) {
			$this->reduceWidth($id);
			
			$data = array_merge($data, $this->getLftRgt($objectiveNodeId, $position));
		}
		
		$primary = $this->getAdapter()->quoteIdentifier($this->_primary[1]);
		$where = $this->getAdapter()->quoteInto($primary . ' = ?', $id, Zend_Db::INT_TYPE);
		
		return $this->update($data, $where);
	}
	
	/**
	 * Deletes some node.
	 *
	 * @param mixed Id of a node.
	 * @param bool Whether to perform cascade deleting.
	 * @return int
	 */
	public function deleteNode($id, $cascade = false)
	{
		$id = (int)$id;
		
		$primary = $this->getAdapter()->quoteIdentifier($this->_primary[1]);

		if ($cascade == false) {
			$this->reduceWidth($id);
			
			$where = $this->getAdapter()->quoteInto($primary . ' = ?', $id, Zend_Db::INT_TYPE);
		
			//Deleting node.
			return $this->delete($where);
		}
		else {
			$tableName = $this->getAdapter()->quoteIdentifier($this->_name);
			$leftCol = $this->getAdapter()->quoteIdentifier($this->_left);
			$rightCol = $this->getAdapter()->quoteIdentifier($this->_right);
			
			$sql = "SELECT $leftCol, $rightCol, ($rightCol - $leftCol + 1) AS 'width' FROM $tableName WHERE $primary = " . $this->getAdapter()->quote($id, Zend_Db::INT_TYPE);
			$result = $this->getAdapter()->fetchRow($sql);
		
			$lft = $result[$this->_left];
			$rgt = $result[$this->_right];
			$width = $result['width'];
			
			$addWhere = '';
			if (strlen($this->addWhere) > 0) {
				$addWhere = ' AND ' . $this->addWhere;
			}
			
			//Deleting items.
			$sql = "DELETE FROM $tableName WHERE ($leftCol BETWEEN $lft AND $rgt)" . $addWhere;
			$stmt = $this->getAdapter()->query($sql);
			$affected = $stmt->rowCount();
			
			$sql = "UPDATE $tableName SET $rightCol = $rightCol - $width WHERE $rightCol > $rgt" . $addWhere;
			$this->getAdapter()->query($sql);
		
			$sql = "UPDATE $tableName SET $leftCol = $leftCol - $width WHERE $leftCol > $lft" . $addWhere;
			$this->getAdapter()->query($sql);
			
			return $affected;
		}
	}
	
	
	
	/**
	 * Generates left and right column value, based on id of a 
	 * objective node.
	 *
	 * @param mixed Id of a objective node.
	 * @param string Position in tree.
	 * @return array
	 */
	protected function getLftRgt($objectiveNodeId, $position)
	{
		$lftRgt = array();
		
		$tableName = $this->getAdapter()->quoteIdentifier($this->_name);
		$leftCol = $this->getAdapter()->quoteIdentifier($this->_left);
		$rightCol = $this->getAdapter()->quoteIdentifier($this->_right);
		
		$addWhere = '';
		
		if ($objectiveNodeId != null) { //User selected some objective node?
			if (strlen($this->addWhere) > 0) {
				$addWhere = ' AND ' . $this->addWhere;
			}
				
			$primary = $this->getAdapter()->quoteIdentifier($this->_primary[1]);
			
			$sql = $this->getAdapter()->quoteInto("SELECT $leftCol, $rightCol FROM $tableName WHERE $primary = ?", $objectiveNodeId, Zend_Db::INT_TYPE);
			$result = $this->getAdapter()->fetchRow($sql);
			$left = $result[$this->_left];
			$right = $result[$this->_right];
			
			$sql1 = '';
			$sql2 = '';
			switch ($position) {
				case self::FIRST_CHILD :
					$sql1 = "UPDATE $tableName SET $rightCol = $rightCol + 2 WHERE $rightCol > $left" . $addWhere;
					$sql2 = "UPDATE $tableName SET $leftCol = $leftCol + 2 WHERE $leftCol > $left" . $addWhere;
					
					$lftRgt[$this->_left] = $left + 1;
					$lftRgt[$this->_right] = $left + 2;
					
					break;
				case self::LAST_CHILD :
					$sql1 = "UPDATE $tableName SET $rightCol = $rightCol + 2 WHERE $rightCol >= $right" . $addWhere;
					$sql2 = "UPDATE $tableName SET $leftCol = $leftCol + 2 WHERE $leftCol > $right" . $addWhere;
					
					$lftRgt[$this->_left] = $right;
					$lftRgt[$this->_right] = $right + 1;
			
					break;
				case self::NEXT_SIBLING :
					$sql1 = "UPDATE $tableName SET $rightCol = $rightCol + 2 WHERE $rightCol > $right" . $addWhere;
					$sql2 = "UPDATE $tableName SET $leftCol = $leftCol + 2 WHERE $leftCol > $right" . $addWhere;
					
					$lftRgt[$this->_left] = $right + 1;
					$lftRgt[$this->_right] = $right + 2;
					
					break;
				case self::PREV_SIBLING :
					$sql1 = "UPDATE $tableName SET $rightCol = $rightCol + 2 WHERE $rightCol > $left" . $addWhere;
					$sql2 = "UPDATE $tableName SET $leftCol = $leftCol + 2 WHERE $leftCol >= $left" . $addWhere;
					
					$lftRgt[$this->_left] = $left;
					$lftRgt[$this->_right] = $left + 1;
					
					break;
			}
			
			$this->getAdapter()->query($sql1);

			$this->getAdapter()->query($sql2);
		}
		else {
			if (strlen($this->addWhere) > 0) {
				$addWhere = ' WHERE ' . $this->addWhere;
			}
				
			$sql = "SELECT MAX($rightCol) AS 'max_rgt' FROM $tableName" . $addWhere;
			$result = $this->getAdapter()->fetchRow($sql);
			
			if ($result == null) { //No data? First node...
				$lftRgt[$this->_left] = 1;
			}
			else {
				$lftRgt[$this->_left] = $result['max_rgt'] + 1;
			}
			
			$lftRgt[$this->_right] = $lftRgt[$this->_left] + 1;
		}
		
		return $lftRgt;
	}
	
	/**
	 * Reduces lft and rgt values of some nodes, on which some 
	 * node that is changing position in tree, or being deleted, 
	 * has effect.
	 *
	 * @param mixed Id of a node.
	 * @return void
	 */
	protected function reduceWidth($id)
	{
		$addWhere = '';
		if (strlen($this->addWhere) > 0) {
			$addWhere = ' AND ' . $this->addWhere;
		}
		
		$tableName = $this->getAdapter()->quoteIdentifier($this->_name);
		$primary = $this->getAdapter()->quoteIdentifier($this->_primary[1]);
		$leftCol = $this->getAdapter()->quoteIdentifier($this->_left);
		$rightCol = $this->getAdapter()->quoteIdentifier($this->_right);
		
		$sql = "SELECT $leftCol, $rightCol, ($rightCol - $leftCol + 1) AS 'width' FROM $tableName WHERE $primary = " . $this->getAdapter()->quote($id, Zend_Db::INT_TYPE);
		$result = $this->getAdapter()->fetchRow($sql);
		
		$left = $result[$this->_left];
		$right = $result[$this->_right];
		$width = $result['width'];
		
		if ((int)$width > 2) { //Some node that has childs.
			//Updating childs.
			$sql = "UPDATE $tableName SET $rightCol = $rightCol - 1, $leftCol = $leftCol - 1 WHERE ($leftCol BETWEEN $left AND $right)" . $addWhere;
			$this->getAdapter()->query($sql);
		}
		
		//Updating parent nodes and nodes on next levels.
		
		$sql = "UPDATE $tableName SET $rightCol = $rightCol - 2 WHERE $rightCol > $right" . $addWhere;
		$this->getAdapter()->query($sql);
	
		$sql = "UPDATE $tableName SET $leftCol = $leftCol - 2 WHERE $leftCol > $left" . $addWhere;
		$this->getAdapter()->query($sql);
	}
	
	/**
	 * Gets id of some node's current objective node.
	 *
	 * @param mixed Node id.
	 * @param string Position in tree.
	 * @return string|null
	 */
	protected function getCurrentObjectiveId($nodeId, $position)
	{	
		$addWhere = '';
		if (strlen($this->addWhere) > 0) {
			$addWhere = ' AND ' . $this->addWhere;
		}
		
		$sql = '';
		
		$tableName = $this->getAdapter()->quoteIdentifier($this->_name);
		$primary = $this->getAdapter()->quoteIdentifier($this->_primary[1]);
		$leftCol = $this->getAdapter()->quoteIdentifier($this->_left);
		$rightCol = $this->getAdapter()->quoteIdentifier($this->_right);
		
		switch ($position) {
			case self::FIRST_CHILD :
				$sql = $this->getAdapter()->quoteInto("SELECT node.$primary 
				FROM $tableName node, (SELECT $leftCol, $rightCol FROM $tableName WHERE $primary = ?) AS current 
				WHERE current.$leftCol BETWEEN node.$leftCol+1 AND node.$rightCol AND current.$leftCol - node.$leftCol = 1" . $addWhere . "
				ORDER BY node.$leftCol DESC", $nodeId, Zend_Db::INT_TYPE);
				
				break;
			case self::LAST_CHILD :
				$sql = $this->getAdapter()->quoteInto("SELECT node.$primary 
				FROM $tableName node, (SELECT $leftCol, $rightCol FROM $tableName WHERE $primary = ?) AS current 
				WHERE current.$leftCol BETWEEN node.$leftCol+1 AND node.$rightCol AND node.$rightCol - current.$rightCol = 1" . $addWhere . "
				ORDER BY node.$leftCol DESC", $nodeId, Zend_Db::INT_TYPE);
				
				break;
			case self::NEXT_SIBLING :
				$sql = $this->getAdapter()->quoteInto("SELECT node.$primary
				FROM $tableName node, (SELECT $leftCol FROM $tableName WHERE $primary = ?) AS current 
				WHERE current.$leftCol - node.$rightCol = 1" . $addWhere, $nodeId, Zend_Db::INT_TYPE);
				
				break;
			case self::PREV_SIBLING :
				$sql = $this->getAdapter()->quoteInto("SELECT node.$primary
				FROM $tableName node, (SELECT $rightCol FROM $tableName WHERE $primary = ?) AS current 
				WHERE node.$leftCol - current.$rightCol = 1" . $addWhere, $nodeId, Zend_Db::INT_TYPE);
				
				break;
		}
		
		$result = $this->getAdapter()->fetchRow($sql);
		
		return $result[$this->_primary[1]];
	}
}
?>
Return current item: Nested Set DB Table