Resolve the infinite classification of left and right values of thinkphp
- 2020-06-15 07:54:18
- OfStack
Before 1 direct use of unlimited parent-child classification, this classification structure is clear and simple to use. However, if the number of categories is large, the query performance is poor. For example, in the navigation menu, I want to query the whole classification tree according to a certain category (ancestors).
Performance is very expensive, either by doing recursion or by doing multiple queries. Therefore, in the case of a large amount of classified data, I recommend the use of left and right values to reduce query headaches.
2: Appends a child node under the current node
Performance is very expensive, either by doing recursion or by doing multiple queries. Therefore, in the case of a large amount of classified data, I recommend the use of left and right values to reduce query headaches.
_id
/**
+----------------------------------------------------------
* The constructor
* @access public
* @return void
+----------------------------------------------------------
*/
public function __construct($left,$right,$id){
parent::__construct();
$this->_left = $left;
$this->_right = $right;
$this->_id = $id;
}
/**
+----------------------------------------------------------
* According to the node$this->_id Get the node All the values
* @access public
* @param $nodeId
* @return array
+----------------------------------------------------------
*/
public function getNodeById($nodeId)
{
if($nodeId>0)
{
return $this->getById($nodeId);
}
else
{
throw_exception(' The unknown $this->_id');
return false;
}
}
/**
+----------------------------------------------------------
* Get the parent node , Including immediate parent ( type=1 ), all parent classes: type=0
* @access public
* @param $nodeId int node $this->_id
* @return $parentNode array()
+----------------------------------------------------------
*/
public function getParentNode($nodeId,$type = 0)
{
if($nodeId == 0) throw_exception(' The unknown $this->_id');;
$currentNode = $this->getNodeById($nodeId);
if($currentNode)
{
$condition = " ".$this->_left.'<'.$currentNode[$this->_left].' and '.$this->_right.' >'.$currentNode[$this->_right]." ";
if($type ==1) // Directly under the parent class
{
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;
}
}
/**
+----------------------------------------------------------
* Total number of children nodes under the current node . Total descendants = (right value of current node - The left value of the current node -1 ) /2
* @access public
* @param $node_id int node $this->_id
* @return $amount int The total number of descendants under this node *
+----------------------------------------------------------
*/
public function getChildCount($nodeId)
{
$currentNode = $this->getNodeById($nodeId);
if(!empty($currentNode))
{
return (int)($currentNode[$this->_right]-$currentNode[$this->_left] -1)/2;
}
}
/**
+----------------------------------------------------------
* Gets all the children under the current node. when A The right node of the subclass =B Subclass left node -1 the A , B Belong to the same 1 level
* @access public
* @param $curentId
* @param $type int 0 : All subclasses under the current node, 1 Is under the current node 1 Class a subclass
* @return bool
+----------------------------------------------------------
*/
public function getChild($nodeId,$type=0)
{
$currentNode = $this->getNodeById($nodeId);
if($currentNode[$this->_left]-$currentNode[$this->_right] ==1)
{
return false; // when The node left value - The right value =1 , there are no children under it.
}
else
{
$condition = $this->_left.'>'.$currentNode[$this->_left].' and '.$this->_right .'<'.$currentNode[$this->_right];
$child = $this->where($condition)->findAll();
if($type == 0)// All subclasses
{
return $child;
}
else if($type ==1) // Gets under the current node 1 Grade classification
{
$subArr = array(); //1 Class a subclass
foreach ($child as $k=>$sub) {
// The left node of a subclass = Parent left node +1 , the subclass is the first 1 A subclass
if($sub[$this->_left]==$currentNode[$this->_left]+1)
{
//$right = $sub[$k][$this->_right]; // Right node of the current node
$firstSub = $sub; // Current node 1 A subclass
array_push($subArr,$firstSub); // Subclasses into the stack
unset($child[$k]);
}
}
$rightVal = $firstSub[$this->_right]; // The first 1 The child node is the comparison flag
$childCount = count($child);// Number of remaining child nodes
for($i=0;$i<$childCount;$i++) // Circular retrieval Sibling node
{
foreach ($child as $key => $sub2) {
if($rightVal == $sub2[$this->_left]-1)
{
$rightVal = $sub2[$this->_right]; // Put the loop current node The right node as the comparison value
array_push($subArr,$sub2);
unset($child[$key]);
}
}
}
return $subArr;
}
}
}
/**
+----------------------------------------------------------
* Returns the full path of the current node
* @access public
* @param $nodeId
* @return array
+----------------------------------------------------------
*/
public function getSinglePath($nodeId)
{
$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;
return $this->query($sql);
}
/**
+----------------------------------------------------------
* Add child nodes and subdivide 3 A: 0 : Last appends under the current node 1 Child node; 1 : Appends a value under the current node 1 Child node;
2: Appends a child node under the current node
* @access public
* @param $currentId int
* @param $nodeName string New node name
* @param $targetId int Appends to the specified node of the current node's child node
* @return bool
+----------------------------------------------------------
*/
public function addNode($nodeId,$newData,$type=0,$targetId=0)
{
if(empty($newData))
{
throw_exception(' The 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;
$rightNode = $leftNode+1;
default:
break;
}
// $sql = "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); // Assign the rvalue of all nodes whose rvalue is greater than the new node's rvalue +2, Pay attention to efficiency
$this->setInc($this->_left,$this->_left.">".$leftNode,2); // Put all left values greater than the new node +2
$newData[$this->_left] = (int)$leftNode;
$newData[$this->_right] =(int) $rightNode;
return $this->add($newData);
}
/**
+----------------------------------------------------------
* Remove nodes
* @access public
* @param type Operation type, default is 0 Delete all children under the current node, 1 To delete a node that includes itself
* @param $nodeId int Want to delete the $this->_id
* @return bool
+----------------------------------------------------------
*/
public function rmNode($nodeId,$type =1)
{
$currentNode = $this->getNodeById($nodeId);
if($type == 1) // Deletes a node that contains itself
{
$sql = "DELETE FROM __TABLE__ WHERE ".$this->_left.">= {$currentNode[$this->_left]} AND ".$this->_right."<= {$currentNode[$this->_right]}";
$childCount = ($this->getChildCount($nodeId)+1)*2; // The value to update
$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 // Deletes all nodes under the current node
{
$sql ="DELETE FROM __TABLE__ WHERE ".$this->_left."> {$currentNode[$this->_left]} AND ".$this->_right."< {$currentNode[$this->_right]}";
$childCount = $this->getChildCount($nodeId)*2; // The value to update
$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 To modify the $this->_id,k-v Must be aligned, such as arr['node_name'] = ' goods '
* @return bool
+----------------------------------------------------------
*/
public function modiNode($newData)
{
if(!empty($newData))
{
$id = $newData[$this->_id];
unset($newData[$this->_id]);
return $this->save($newData,$this->_id.'='.$id);
}
}
}
?>