<?php
namespace Moto\Application\Util\Tree; use Moto\Application\Model\AbstractTable; use Zend\Db\Sql\Insert; use Zend\Db\Sql\Where; class NestedSet extends Tree { protected $_options = array( 'left_key' => 'lft', 'right_key' => 'rgt', 'level' => 'level' ); public function __construct(AbstractTable $context, $options = array()) { parent::__construct($context, $options); } public function insert(array $data) { $tableName = $this->_context->table; $lftField = $this->_options['left_key']; $rgtField = $this->_options['right_key']; $levelField = $this->_options['level']; if ($data['parent_id']) { $parentNode = $this->_context->select(array('id' => $data['parent_id']))->current(); $lft = $parentNode[$lftField]; $rgt = $parentNode[$rgtField]; $level = $parentNode[$levelField]; $sql = "UPDATE `$tableName`
                SET $rgtField = $rgtField + 2,
                    lft = IF($lftField > $rgt, $lftField + 2, $lftField)
                WHERE $rgtField >= $rgt"; $this->_query($sql); } else { $sql = "SELECT MAX($rgtField) as max_rgt FROM `$tableName`"; $maxRgt = $this->_query($sql)->current(); $rgt = $maxRgt['max_rgt'] + 1; $level = -1; } $data[$lftField] = $rgt; $data[$rgtField] = $rgt + 1; $data[$levelField] = $level + 1; $insert = new Insert($tableName); $insert->values($data); return $this->_context->insertWith($insert); } public function getParentBranch($id) { $tableName = $this->_context->table; $lftField = $this->_options['left_key']; $rgtField = $this->_options['right_key']; $sql = "SELECT * FROM $tableName p1, $tableName p2
                WHERE p2.$lftField <= p1.$lftField
                AND p2.$rgtField >= p1.$rgtField
                AND p1.id = $id
                ORDER BY p2.$lftField"; return $this->_query($sql); } public function delete($where) { $tableName = $this->_context->table; $lftField = $this->_options['left_key']; $rgtField = $this->_options['right_key']; $nodes = $this->_context->select($where)->toArray(); foreach ($nodes as $node) { $lft = $node['lft']; $rgt = $node['rgt']; $sql = "DELETE FROM `$tableName` WHERE $lftField >= $lft AND $rgtField <= $rgt"; $this->_query($sql); $queryString = "UPDATE %s
                            SET %s = CASE WHEN %s > %s THEN %s - (%s - %s + 1) ELSE %s END,
                            %s = %s - (%s - %s + 1)
                            WHERE %s > %s"; $sql = sprintf($queryString, $tableName, $lftField, $lftField, $lft, $lftField, $rgt, $lft, $lftField, $rgtField, $rgtField, $rgt, $lft, $rgtField, $rgt); $this->_query($sql); } } public function moveNode($id, $newParentId) { } }