Analyze thinkphp's left and right value infinite classification_PHP tutorial

In the past, the infinite classification of father and son has been used. This classification structure is clear and easy to use. However, if the number of categories is large, the query performance will be poor. For example, when making a navigation menu, I want to query the entire classification tree (ancestors) based on a certain classification.
The performance consumption is very large, either recursive or multiple queries. Therefore, for situations where the amount of classified data is large, I recommend using left and right values ​​to reduce query trouble.

Copy code The code is as follows:

_id
    /**
+------------------------------------------------- ------------
* Constructor
* @access public
* @return void
+------------- -----------------------------------------------
           */
    public  function __construct($left,$right,$id){
        parent::__construct();
       $this->_left = $left;
       $this->_right = $right;
       $this->_id = $id;
    }
    /**
+------------------------------------------------- ------------
* Get all the values ​​of the node based on node$this->_id
* @access public
* @param $nodeId
* @ return array
+-------------------------------------------------- ---------------
*/    
    public  function getNodeById($nodeId)
    {
        if($nodeId>0)
        {
            return $this->getById($nodeId);
        }
        else
        {
            throw_exception('未知$this->_id');
            return false;
        }
    }
    /**
                       +------------------------------------------------- ------------
* Get the parent node, including direct parent class (type=1), all parent classes: type=0
* @access public
* @param $nodeId int node $this->_id
                                                                       ----------------------------------
         */    
    public  function getParentNode($nodeId,$type = 0)
    {
        if($nodeId == 0) throw_exception('未知$this->_id');;
        $currentNode = $this->getNodeById($nodeId);
        if($currentNode)
        {
            $condition = " ".$this->_left.'<'.$currentNode[$this->_left].' and '.$this->_right.' >'.$currentNode[$this->_right]." ";
            if($type ==1) //直属父类
            {
                return $this->where($condition)->order($this->_left." DESC")->limit(1)->find();
                //                $sql = "SELECT * FROM ".TABLE_NAME." WHERE {$condition} ORDER BY ".$this->_left." DESC LIMIT 1";
                //                return mysql_query($sql) or die(mysql_error());
            }
            else if($type ==0)
            {
                return $this->where($condition)->findAll();
                //                $sql = "SELECT * FROM ".TABLE_NAME." WHERE {$condition} ";
                //                return mysql_query($sql) or die(mysql_error());
            }
        }
        else
        {
            return false;
        }
    }
    /**
+------------------------------------------------- ------------
* The total number of descendant nodes under the current node. The total number of descendants = (rvalue of the current node - lvalue of the current node - 1)/2
* @access public
* @param $node_id int Node $this->_id
* @return $amount int The total number of descendants of this node *
+-------------- -----------------------------------------------
           */
    public  function getChildCount($nodeId)
    {
        $currentNode = $this->getNodeById($nodeId);
        if(!empty($currentNode))
        {
            return (int)($currentNode[$this->_right]-$currentNode[$this->_left] -1)/2;
        }
    }
    /**
+------------------------------------------------- ------------
* Get all child nodes under the current node. When the right node of subclass A = the left node of subclass B - 1, then A and B belong to the same level
* @access public
* @param $curentId
* @param $type int 0: current node All subcategories under the current node, 1 is the next subcategory of the current node
* @return bool
+------------------------- ----------------------------------
*/    
    public  function getChild($nodeId,$type=0)
    {
        $currentNode = $this->getNodeById($nodeId);
        if($currentNode[$this->_left]-$currentNode[$this->_right] ==1)
        {
            return false; //当 该节点左值 - 右值=1  时，其下没有子节点。
} Else
{
$ condition = $ this- & gt; _let. '& Gt;'. $ CanNotnode [$ this- & gt; _let]. _right .'<'.$currentNode[$this->_right];
                  $child = $this->where($condition)->findAll();
                                                              "                                                                          $ subarr = array (); // The first -level subclass
Foreach ($ Child as $ k = & gt; $ sub) {
// Sub -class left node = parent -class left node +1, then the child The class is the first subclass
if($sub[$this->_left]==$currentNode[$this->_left]+1)
= $ sub[$k][$this->_right]; //The right node of the current node
                                                                                                                                                                                                                                                                 ); // Subclasses are pushed onto the stack $firstSub[$this->_right]; //first The child nodes are comparison marks
$childCount = count($child);//The number of remaining child nodes
for($i=0;$i<$childCount;$i++) //Loop to retrieve the same level Child Node 🎜>                                                                                                             🎜>                        $rightVal = $sub2[$this->_right]; // Use the right node of the current node in the loop as the comparison value                                                                                                                                                                                                     key]);
                                                                                                                                  
            return $subArr; 🎜> {
$sql = "select parent.* from __TABLE__ as node,__TABLE__ as parent where node.{$this->_left} between parent.{$this->_left}
    AND parent.{$this->_right} AND node.{$this->_id} = {$nodeId} order by parent.{$this->_left}";
//                                   echo $sql; sql);
}
/**
+---------------------------------- -----------------------
* Add a child node, divided into 3 types: 0: add the last child node under the current node; 1 : Append the first child node under the current node;


2: Append after a child node under the current node



Copy code

The code is as follows:

* @access public
* @param $currentId int
* @param $nodeName string New node name
* @param $targetId int appended to the specified node of the child node under the current node
* @return bool
+--------------------------------------- ------------------
*/
public function addNode($nodeId,$newData,$type=0,$targetId=0)
{
if(empty($newData))
{
throw_exception('New category cannot be empty');
}
$currentNode = $this->getNodeById($nodeId) ;
switch ($type) {
case 0:
$leftNode = $currentNode[$this->_right]; //The left value of the new node is the right value of the parent node
$ rightNode = $leftNode+1;
break;
case 1:
$leftNode = $currentNode[$this->_left]+1; //The left value of the new node is the left value of the parent node +1
$rightNode = $leftNode+1;
break;
case 2:
$otherNode = $this->getNodeById($targetId );
                       $leftNode = $otherNode[ $this->_right]+1;
                                                                                                       = "UPDATE ".TABLE_NAME." SET ".$this->_right."=".$this->_right."+2 WHERE ".$this->_right." >= ".$leftNode;
//        $sql2 = "UPDATE ".TABLE_NAME." SET ".$this->_left."=".$this->_left."+2 WHERE ".$this->_left.">".$leftNode;
          $this->setInc($this->_right,$this->_right.">=".$leftNode,2); //Set all nodes whose right value is greater than the left value of the new node Rvalue + 2, pay attention to efficiency
                                                                                                                                                                                                                            $this->setInc($this->_left,$this->_left.">".$leftNode,2); //Set all nodes greater than the new node Left value + 2
           $newData[$this->_left] = (int)$leftNode;
                                                                                                                   Left value ->add($newData);
}
    /**
+------------------------------------------------- ------------
* Delete node
* @access public
* @param type Operation type, the default is 0 to delete all child nodes under the current node, 1 to delete Including its own node
* @param $nodeId int $this->_id
to be deleted * @return bool
                                                   ----------------------------------------
*/    
    public  function rmNode($nodeId,$type =1)
    {
        $currentNode = $this->getNodeById($nodeId);
        if($type == 1) //删除包含自身的节点
        {
            $sql = "DELETE FROM __TABLE__ WHERE ".$this->_left.">= {$currentNode[$this->_left]} AND ".$this->_right."<= {$currentNode[$this->_right]}";
            $childCount = ($this->getChildCount($nodeId)+1)*2; //要更新的值
            $sql2 = "UPDATE  __TABLE__  SET ".$this->_right."=".$this->_right."-".$childCount." WHERE ".$this->_right.">".$currentNode[$this->_right];
            $sql3 = "UPDATE  __TABLE__  SET ".$this->_left."=".$this->_left."-".$childCount." WHERE ".$this->_left.">".$currentNode[$this->_left];
        }
        else //删除当前节点下的所有节点
        {
            $sql ="DELETE FROM __TABLE__ WHERE ".$this->_left."> {$currentNode[$this->_left]} AND ".$this->_right."< {$currentNode[$this->_right]}";
            $childCount = $this->getChildCount($nodeId)*2; //要更新的值
            $sql2 = "UPDATE __TABLE__ SET ".$this->_right."=".$this->_right ."-".$childCount." WHERE ".$this->_right.">=".$currentNode[$this->_right];
            $sql3 = "UPDATE __TABLE__ SET ".$this->_left."=".$this->_left."-".$childCount." WHERE ".$this->_left.">".$currentNode[$this->_left];
        }
         $this->execute($sql); 
         $this->execute($sql2); 
         $this->execute($sql3); 
        return true;
    }
/**
+------------------------------------------------- ------------
* Modify nodes, names, etc.
* @access public
* @param $newData array() must contain the $this->_id to be modified ,k-v must be aligned, such as arr['node_name'] = 'Product'
* @return bool
+--------------------- ----------------------------------
*/
public function modiNode($newData)
{
if(!empty($newData))
{
$id = $newData[ $ this-& gt; _id];
UNSET ($ newdata [$ this-& gt; _id]); $id);



http://www.bkjia.com/PHPjc/327715.html

www.bkjia.com

true

http: //www.bkjia.com/PHPjc/327715.html

TechArticleI have always used the parent-child infinite classification. This classification structure is clear and easy to use. However, if the number of categories is large, the query performance will be poor. For example, when making a navigation menu, I want to root...