1<?php 2/** 3 * @package FrameworkOnFramework 4 * @subpackage table 5 * @copyright Copyright (C) 2010-2016 Nicholas K. Dionysopoulos / Akeeba Ltd. All rights reserved. 6 * @license GNU General Public License version 2 or later; see LICENSE.txt 7 */ 8 9// Protect from unauthorized access 10defined('FOF_INCLUDED') or die; 11 12/** 13 * A class to manage tables holding nested sets (hierarchical data) 14 * 15 * @property int $lft Left value (for nested set implementation) 16 * @property int $rgt Right value (for nested set implementation) 17 * @property string $hash Slug hash (optional; for faster searching) 18 * @property string $slug Node's slug (optional) 19 * @property string $title Title of the node (optional) 20 */ 21class FOFTableNested extends FOFTable 22{ 23 /** @var int The level (depth) of this node in the tree */ 24 protected $treeDepth = null; 25 26 /** @var FOFTableNested The root node in the tree */ 27 protected $treeRoot = null; 28 29 /** @var FOFTableNested The parent node of ourselves */ 30 protected $treeParent = null; 31 32 /** @var bool Should I perform a nested get (used to query ascendants/descendants) */ 33 protected $treeNestedGet = false; 34 35 /** @var array A collection of custom, additional where clauses to apply during buildQuery */ 36 protected $whereClauses = array(); 37 38 /** 39 * Public constructor. Overrides the parent constructor, making sure there are lft/rgt columns which make it 40 * compatible with nested sets. 41 * 42 * @param string $table Name of the database table to model. 43 * @param string $key Name of the primary key field in the table. 44 * @param FOFDatabaseDriver &$db Database driver 45 * @param array $config The configuration parameters array 46 * 47 * @throws \RuntimeException When lft/rgt columns are not found 48 */ 49 public function __construct($table, $key, &$db, $config = array()) 50 { 51 parent::__construct($table, $key, $db, $config); 52 53 if (!$this->hasField('lft') || !$this->hasField('rgt')) 54 { 55 throw new \RuntimeException("Table " . $this->getTableName() . " is not compatible with FOFTableNested: it does not have lft/rgt columns"); 56 } 57 } 58 59 /** 60 * Overrides the automated table checks to handle the 'hash' column for faster searching 61 * 62 * @return boolean 63 */ 64 public function check() 65 { 66 // Create a slug if there is a title and an empty slug 67 if ($this->hasField('title') && $this->hasField('slug') && empty($this->slug)) 68 { 69 $this->slug = FOFStringUtils::toSlug($this->title); 70 } 71 72 // Create the SHA-1 hash of the slug for faster searching (make sure the hash column is CHAR(64) to take 73 // advantage of MySQL's optimised searching for fixed size CHAR columns) 74 if ($this->hasField('hash') && $this->hasField('slug')) 75 { 76 $this->hash = sha1($this->slug); 77 } 78 79 // Reset cached values 80 $this->resetTreeCache(); 81 82 return parent::check(); 83 } 84 85 /** 86 * Delete a node, either the currently loaded one or the one specified in $id. If an $id is specified that node 87 * is loaded before trying to delete it. In the end the data model is reset. If the node has any children nodes 88 * they will be removed before the node itself is deleted. 89 * 90 * @param integer $oid The primary key value of the item to delete 91 * 92 * @throws UnexpectedValueException 93 * 94 * @return boolean True on success 95 */ 96 public function delete($oid = null) 97 { 98 // Load the specified record (if necessary) 99 if (!empty($oid)) 100 { 101 $this->load($oid); 102 } 103 104 $k = $this->_tbl_key; 105 $pk = (!$oid) ? $this->$k : $oid; 106 107 // If no primary key is given, return false. 108 if (!$pk) 109 { 110 throw new UnexpectedValueException('Null primary key not allowed.'); 111 } 112 113 // Execute the logic only if I have a primary key, otherwise I could have weird results 114 // Perform the checks on the current node *BEFORE* starting to delete the children 115 if (!$this->onBeforeDelete($oid)) 116 { 117 return false; 118 } 119 120 $result = true; 121 122 // Recursively delete all children nodes as long as we are not a leaf node and $recursive is enabled 123 if (!$this->isLeaf()) 124 { 125 // Get all sub-nodes 126 $table = $this->getClone(); 127 $table->bind($this->getData()); 128 $subNodes = $table->getDescendants(); 129 130 // Delete all subnodes (goes through the model to trigger the observers) 131 if (!empty($subNodes)) 132 { 133 /** @var FOFTableNested $item */ 134 foreach ($subNodes as $item) 135 { 136 // We have to pass the id, so we are getting it again from the database. 137 // We have to do in this way, since a previous child could have changed our lft and rgt values 138 if(!$item->delete($item->$k)) 139 { 140 // A subnode failed or prevents the delete, continue deleting other nodes, 141 // but preserve the current node (ie the parent) 142 $result = false; 143 } 144 }; 145 146 // Load it again, since while deleting a children we could have updated ourselves, too 147 $this->load($pk); 148 } 149 } 150 151 if($result) 152 { 153 // Delete the row by primary key. 154 $query = $this->_db->getQuery(true); 155 $query->delete(); 156 $query->from($this->_tbl); 157 $query->where($this->_tbl_key . ' = ' . $this->_db->q($pk)); 158 159 $this->_db->setQuery($query)->execute(); 160 161 $result = $this->onAfterDelete($oid); 162 } 163 164 return $result; 165 } 166 167 protected function onAfterDelete($oid) 168 { 169 $db = $this->getDbo(); 170 171 $myLeft = $this->lft; 172 $myRight = $this->rgt; 173 174 $fldLft = $db->qn($this->getColumnAlias('lft')); 175 $fldRgt = $db->qn($this->getColumnAlias('rgt')); 176 177 // Move all siblings to the left 178 $width = $this->rgt - $this->lft + 1; 179 180 // Wrap everything in a transaction 181 $db->transactionStart(); 182 183 try 184 { 185 // Shrink lft values 186 $query = $db->getQuery(true) 187 ->update($db->qn($this->getTableName())) 188 ->set($fldLft . ' = ' . $fldLft . ' - '.$width) 189 ->where($fldLft . ' > ' . $db->q($myLeft)); 190 $db->setQuery($query)->execute(); 191 192 // Shrink rgt values 193 $query = $db->getQuery(true) 194 ->update($db->qn($this->getTableName())) 195 ->set($fldRgt . ' = ' . $fldRgt . ' - '.$width) 196 ->where($fldRgt . ' > ' . $db->q($myRight)); 197 $db->setQuery($query)->execute(); 198 199 // Commit the transaction 200 $db->transactionCommit(); 201 } 202 catch (\Exception $e) 203 { 204 // Roll back the transaction on error 205 $db->transactionRollback(); 206 207 throw $e; 208 } 209 210 return parent::onAfterDelete($oid); 211 } 212 213 /** 214 * Not supported in nested sets 215 * 216 * @param string $where Ignored 217 * 218 * @return void 219 * 220 * @throws RuntimeException 221 */ 222 public function reorder($where = '') 223 { 224 throw new RuntimeException('reorder() is not supported by FOFTableNested'); 225 } 226 227 /** 228 * Not supported in nested sets 229 * 230 * @param integer $delta Ignored 231 * @param string $where Ignored 232 * 233 * @return void 234 * 235 * @throws RuntimeException 236 */ 237 public function move($delta, $where = '') 238 { 239 throw new RuntimeException('move() is not supported by FOFTableNested'); 240 } 241 242 /** 243 * Create a new record with the provided data. It is inserted as the last child of the current node's parent 244 * 245 * @param array $data The data to use in the new record 246 * 247 * @return static The new node 248 */ 249 public function create($data) 250 { 251 $newNode = $this->getClone(); 252 $newNode->reset(); 253 $newNode->bind($data); 254 255 if ($this->isRoot()) 256 { 257 return $newNode->insertAsChildOf($this); 258 } 259 else 260 { 261 return $newNode->insertAsChildOf($this->getParent()); 262 } 263 } 264 265 /** 266 * Makes a copy of the record, inserting it as the last child of the given node's parent. 267 * 268 * @param integer|array $cid The primary key value (or values) or the record(s) to copy. 269 * If null, the current record will be copied 270 * 271 * @return self|FOFTableNested The last copied node 272 */ 273 public function copy($cid = null) 274 { 275 //We have to cast the id as array, or the helper function will return an empty set 276 if($cid) 277 { 278 $cid = (array) $cid; 279 } 280 281 FOFUtilsArray::toInteger($cid); 282 $k = $this->_tbl_key; 283 284 if (count($cid) < 1) 285 { 286 if ($this->$k) 287 { 288 $cid = array($this->$k); 289 } 290 else 291 { 292 // Even if it's null, let's still create the record 293 $this->create($this->getData()); 294 295 return $this; 296 } 297 } 298 299 foreach ($cid as $item) 300 { 301 // Prevent load with id = 0 302 303 if (!$item) 304 { 305 continue; 306 } 307 308 $this->load($item); 309 310 $this->create($this->getData()); 311 } 312 313 return $this; 314 } 315 316 /** 317 * Method to reset class properties to the defaults set in the class 318 * definition. It will ignore the primary key as well as any private class 319 * properties. 320 * 321 * @return void 322 */ 323 public function reset() 324 { 325 $this->resetTreeCache(); 326 327 parent::reset(); 328 } 329 330 /** 331 * Insert the current node as a tree root. It is a good idea to never use this method, instead providing a root node 332 * in your schema installation and then sticking to only one root. 333 * 334 * @return self 335 */ 336 public function insertAsRoot() 337 { 338 // You can't insert a node that is already saved i.e. the table has an id 339 if($this->getId()) 340 { 341 throw new RuntimeException(__METHOD__.' can be only used with new nodes'); 342 } 343 344 // First we need to find the right value of the last parent, a.k.a. the max(rgt) of the table 345 $db = $this->getDbo(); 346 347 // Get the lft/rgt names 348 $fldRgt = $db->qn($this->getColumnAlias('rgt')); 349 350 $query = $db->getQuery(true) 351 ->select('MAX(' . $fldRgt . ')') 352 ->from($db->qn($this->getTableName())); 353 $maxRgt = $db->setQuery($query, 0, 1)->loadResult(); 354 355 if (empty($maxRgt)) 356 { 357 $maxRgt = 0; 358 } 359 360 $this->lft = ++$maxRgt; 361 $this->rgt = ++$maxRgt; 362 363 $this->store(); 364 365 return $this; 366 } 367 368 /** 369 * Insert the current node as the first (leftmost) child of a parent node. 370 * 371 * WARNING: If it's an existing node it will be COPIED, not moved. 372 * 373 * @param FOFTableNested $parentNode The node which will become our parent 374 * 375 * @return $this for chaining 376 * 377 * @throws Exception 378 * @throws RuntimeException 379 */ 380 public function insertAsFirstChildOf(FOFTableNested &$parentNode) 381 { 382 if($parentNode->lft >= $parentNode->rgt) 383 { 384 throw new RuntimeException('Invalid position values for the parent node'); 385 } 386 387 // Get a reference to the database 388 $db = $this->getDbo(); 389 390 // Get the field names 391 $fldRgt = $db->qn($this->getColumnAlias('rgt')); 392 $fldLft = $db->qn($this->getColumnAlias('lft')); 393 394 // Nullify the PK, so a new record will be created 395 $pk = $this->getKeyName(); 396 $this->$pk = null; 397 398 // Get the value of the parent node's rgt 399 $myLeft = $parentNode->lft; 400 401 // Update my lft/rgt values 402 $this->lft = $myLeft + 1; 403 $this->rgt = $myLeft + 2; 404 405 // Update parent node's right (we added two elements in there, remember?) 406 $parentNode->rgt += 2; 407 408 // Wrap everything in a transaction 409 $db->transactionStart(); 410 411 try 412 { 413 // Make a hole (2 queries) 414 $query = $db->getQuery(true) 415 ->update($db->qn($this->getTableName())) 416 ->set($fldLft . ' = ' . $fldLft . '+2') 417 ->where($fldLft . ' > ' . $db->q($myLeft)); 418 $db->setQuery($query)->execute(); 419 420 $query = $db->getQuery(true) 421 ->update($db->qn($this->getTableName())) 422 ->set($fldRgt . ' = ' . $fldRgt . '+ 2') 423 ->where($fldRgt . '>' . $db->q($myLeft)); 424 $db->setQuery($query)->execute(); 425 426 // Insert the new node 427 $this->store(); 428 429 // Commit the transaction 430 $db->transactionCommit(); 431 } 432 catch (\Exception $e) 433 { 434 // Roll back the transaction on error 435 $db->transactionRollback(); 436 437 throw $e; 438 } 439 440 return $this; 441 } 442 443 /** 444 * Insert the current node as the last (rightmost) child of a parent node. 445 * 446 * WARNING: If it's an existing node it will be COPIED, not moved. 447 * 448 * @param FOFTableNested $parentNode The node which will become our parent 449 * 450 * @return $this for chaining 451 * 452 * @throws Exception 453 * @throws RuntimeException 454 */ 455 public function insertAsLastChildOf(FOFTableNested &$parentNode) 456 { 457 if($parentNode->lft >= $parentNode->rgt) 458 { 459 throw new RuntimeException('Invalid position values for the parent node'); 460 } 461 462 // Get a reference to the database 463 $db = $this->getDbo(); 464 465 // Get the field names 466 $fldRgt = $db->qn($this->getColumnAlias('rgt')); 467 $fldLft = $db->qn($this->getColumnAlias('lft')); 468 469 // Nullify the PK, so a new record will be created 470 $pk = $this->getKeyName(); 471 $this->$pk = null; 472 473 // Get the value of the parent node's lft 474 $myRight = $parentNode->rgt; 475 476 // Update my lft/rgt values 477 $this->lft = $myRight; 478 $this->rgt = $myRight + 1; 479 480 // Update parent node's right (we added two elements in there, remember?) 481 $parentNode->rgt += 2; 482 483 // Wrap everything in a transaction 484 $db->transactionStart(); 485 486 try 487 { 488 // Make a hole (2 queries) 489 $query = $db->getQuery(true) 490 ->update($db->qn($this->getTableName())) 491 ->set($fldRgt . ' = ' . $fldRgt . '+2') 492 ->where($fldRgt . '>=' . $db->q($myRight)); 493 $db->setQuery($query)->execute(); 494 495 $query = $db->getQuery(true) 496 ->update($db->qn($this->getTableName())) 497 ->set($fldLft . ' = ' . $fldLft . '+2') 498 ->where($fldLft . '>' . $db->q($myRight)); 499 $db->setQuery($query)->execute(); 500 501 // Insert the new node 502 $this->store(); 503 504 // Commit the transaction 505 $db->transactionCommit(); 506 } 507 catch (\Exception $e) 508 { 509 // Roll back the transaction on error 510 $db->transactionRollback(); 511 512 throw $e; 513 } 514 515 return $this; 516 } 517 518 /** 519 * Alias for insertAsLastchildOf 520 * 521 * @codeCoverageIgnore 522 * @param FOFTableNested $parentNode 523 * 524 * @return $this for chaining 525 * 526 * @throws Exception 527 */ 528 public function insertAsChildOf(FOFTableNested &$parentNode) 529 { 530 return $this->insertAsLastChildOf($parentNode); 531 } 532 533 /** 534 * Insert the current node to the left of (before) a sibling node 535 * 536 * WARNING: If it's an existing node it will be COPIED, not moved. 537 * 538 * @param FOFTableNested $siblingNode We will be inserted before this node 539 * 540 * @return $this for chaining 541 * 542 * @throws Exception 543 * @throws RuntimeException 544 */ 545 public function insertLeftOf(FOFTableNested &$siblingNode) 546 { 547 if($siblingNode->lft >= $siblingNode->rgt) 548 { 549 throw new RuntimeException('Invalid position values for the sibling node'); 550 } 551 552 // Get a reference to the database 553 $db = $this->getDbo(); 554 555 // Get the field names 556 $fldRgt = $db->qn($this->getColumnAlias('rgt')); 557 $fldLft = $db->qn($this->getColumnAlias('lft')); 558 559 // Nullify the PK, so a new record will be created 560 $pk = $this->getKeyName(); 561 $this->$pk = null; 562 563 // Get the value of the parent node's rgt 564 $myLeft = $siblingNode->lft; 565 566 // Update my lft/rgt values 567 $this->lft = $myLeft; 568 $this->rgt = $myLeft + 1; 569 570 // Update sibling's lft/rgt values 571 $siblingNode->lft += 2; 572 $siblingNode->rgt += 2; 573 574 $db->transactionStart(); 575 576 try 577 { 578 $db->setQuery( 579 $db->getQuery(true) 580 ->update($db->qn($this->getTableName())) 581 ->set($fldLft . ' = ' . $fldLft . '+2') 582 ->where($fldLft . ' >= ' . $db->q($myLeft)) 583 )->execute(); 584 585 $db->setQuery( 586 $db->getQuery(true) 587 ->update($db->qn($this->getTableName())) 588 ->set($fldRgt . ' = ' . $fldRgt . '+2') 589 ->where($fldRgt . ' > ' . $db->q($myLeft)) 590 )->execute(); 591 592 $this->store(); 593 594 // Commit the transaction 595 $db->transactionCommit(); 596 } 597 catch (\Exception $e) 598 { 599 $db->transactionRollback(); 600 601 throw $e; 602 } 603 604 return $this; 605 } 606 607 /** 608 * Insert the current node to the right of (after) a sibling node 609 * 610 * WARNING: If it's an existing node it will be COPIED, not moved. 611 * 612 * @param FOFTableNested $siblingNode We will be inserted after this node 613 * 614 * @return $this for chaining 615 * @throws Exception 616 * @throws RuntimeException 617 */ 618 public function insertRightOf(FOFTableNested &$siblingNode) 619 { 620 if($siblingNode->lft >= $siblingNode->rgt) 621 { 622 throw new RuntimeException('Invalid position values for the sibling node'); 623 } 624 625 // Get a reference to the database 626 $db = $this->getDbo(); 627 628 // Get the field names 629 $fldRgt = $db->qn($this->getColumnAlias('rgt')); 630 $fldLft = $db->qn($this->getColumnAlias('lft')); 631 632 // Nullify the PK, so a new record will be created 633 $pk = $this->getKeyName(); 634 $this->$pk = null; 635 636 // Get the value of the parent node's lft 637 $myRight = $siblingNode->rgt; 638 639 // Update my lft/rgt values 640 $this->lft = $myRight + 1; 641 $this->rgt = $myRight + 2; 642 643 $db->transactionStart(); 644 645 try 646 { 647 $db->setQuery( 648 $db->getQuery(true) 649 ->update($db->qn($this->getTableName())) 650 ->set($fldRgt . ' = ' . $fldRgt . '+2') 651 ->where($fldRgt . ' > ' . $db->q($myRight)) 652 )->execute(); 653 654 $db->setQuery( 655 $db->getQuery(true) 656 ->update($db->qn($this->getTableName())) 657 ->set($fldLft . ' = ' . $fldLft . '+2') 658 ->where($fldLft . ' > ' . $db->q($myRight)) 659 )->execute(); 660 661 $this->store(); 662 663 // Commit the transaction 664 $db->transactionCommit(); 665 } 666 catch (\Exception $e) 667 { 668 $db->transactionRollback(); 669 670 throw $e; 671 } 672 673 return $this; 674 } 675 676 /** 677 * Alias for insertRightOf 678 * 679 * @codeCoverageIgnore 680 * @param FOFTableNested $siblingNode 681 * 682 * @return $this for chaining 683 */ 684 public function insertAsSiblingOf(FOFTableNested &$siblingNode) 685 { 686 return $this->insertRightOf($siblingNode); 687 } 688 689 /** 690 * Move the current node (and its subtree) one position to the left in the tree, i.e. before its left-hand sibling 691 * 692 * @throws RuntimeException 693 * 694 * @return $this 695 */ 696 public function moveLeft() 697 { 698 // Sanity checks on current node position 699 if($this->lft >= $this->rgt) 700 { 701 throw new RuntimeException('Invalid position values for the current node'); 702 } 703 704 // If it is a root node we will not move the node (roots don't participate in tree ordering) 705 if ($this->isRoot()) 706 { 707 return $this; 708 } 709 710 // Are we already the leftmost node? 711 $parentNode = $this->getParent(); 712 713 if ($parentNode->lft == ($this->lft - 1)) 714 { 715 return $this; 716 } 717 718 // Get the sibling to the left 719 $db = $this->getDbo(); 720 $table = $this->getClone(); 721 $table->reset(); 722 $leftSibling = $table->whereRaw($db->qn($this->getColumnAlias('rgt')) . ' = ' . $db->q($this->lft - 1)) 723 ->get(0, 1)->current(); 724 725 // Move the node 726 if (is_object($leftSibling) && ($leftSibling instanceof FOFTableNested)) 727 { 728 return $this->moveToLeftOf($leftSibling); 729 } 730 731 return false; 732 } 733 734 /** 735 * Move the current node (and its subtree) one position to the right in the tree, i.e. after its right-hand sibling 736 * 737 * @throws RuntimeException 738 * 739 * @return $this 740 */ 741 public function moveRight() 742 { 743 // Sanity checks on current node position 744 if($this->lft >= $this->rgt) 745 { 746 throw new RuntimeException('Invalid position values for the current node'); 747 } 748 749 // If it is a root node we will not move the node (roots don't participate in tree ordering) 750 if ($this->isRoot()) 751 { 752 return $this; 753 } 754 755 // Are we already the rightmost node? 756 $parentNode = $this->getParent(); 757 758 if ($parentNode->rgt == ($this->rgt + 1)) 759 { 760 return $this; 761 } 762 763 // Get the sibling to the right 764 $db = $this->getDbo(); 765 766 $table = $this->getClone(); 767 $table->reset(); 768 $rightSibling = $table->whereRaw($db->qn($this->getColumnAlias('lft')) . ' = ' . $db->q($this->rgt + 1)) 769 ->get(0, 1)->current(); 770 771 // Move the node 772 if (is_object($rightSibling) && ($rightSibling instanceof FOFTableNested)) 773 { 774 return $this->moveToRightOf($rightSibling); 775 } 776 777 return false; 778 } 779 780 /** 781 * Moves the current node (and its subtree) to the left of another node. The other node can be in a different 782 * position in the tree or even under a different root. 783 * 784 * @param FOFTableNested $siblingNode 785 * 786 * @return $this for chaining 787 * 788 * @throws Exception 789 * @throws RuntimeException 790 */ 791 public function moveToLeftOf(FOFTableNested $siblingNode) 792 { 793 // Sanity checks on current and sibling node position 794 if($this->lft >= $this->rgt) 795 { 796 throw new RuntimeException('Invalid position values for the current node'); 797 } 798 799 if($siblingNode->lft >= $siblingNode->rgt) 800 { 801 throw new RuntimeException('Invalid position values for the sibling node'); 802 } 803 804 $db = $this->getDbo(); 805 $left = $db->qn($this->getColumnAlias('lft')); 806 $right = $db->qn($this->getColumnAlias('rgt')); 807 808 // Get node metrics 809 $myLeft = $this->lft; 810 $myRight = $this->rgt; 811 $myWidth = $myRight - $myLeft + 1; 812 813 // Get sibling metrics 814 $sibLeft = $siblingNode->lft; 815 816 // Start the transaction 817 $db->transactionStart(); 818 819 try 820 { 821 // Temporary remove subtree being moved 822 $query = $db->getQuery(true) 823 ->update($db->qn($this->getTableName())) 824 ->set("$left = " . $db->q(0) . " - $left") 825 ->set("$right = " . $db->q(0) . " - $right") 826 ->where($left . ' >= ' . $db->q($myLeft)) 827 ->where($right . ' <= ' . $db->q($myRight)); 828 $db->setQuery($query)->execute(); 829 830 // Close hole left behind 831 $query = $db->getQuery(true) 832 ->update($db->qn($this->getTableName())) 833 ->set($left . ' = ' . $left . ' - ' . $db->q($myWidth)) 834 ->where($left . ' > ' . $db->q($myRight)); 835 $db->setQuery($query)->execute(); 836 837 $query = $db->getQuery(true) 838 ->update($db->qn($this->getTableName())) 839 ->set($right . ' = ' . $right . ' - ' . $db->q($myWidth)) 840 ->where($right . ' > ' . $db->q($myRight)); 841 $db->setQuery($query)->execute(); 842 843 // Make a hole for the new items 844 $newSibLeft = ($sibLeft > $myRight) ? $sibLeft - $myWidth : $sibLeft; 845 846 $query = $db->getQuery(true) 847 ->update($db->qn($this->getTableName())) 848 ->set($right . ' = ' . $right . ' + ' . $db->q($myWidth)) 849 ->where($right . ' >= ' . $db->q($newSibLeft)); 850 $db->setQuery($query)->execute(); 851 852 $query = $db->getQuery(true) 853 ->update($db->qn($this->getTableName())) 854 ->set($left . ' = ' . $left . ' + ' . $db->q($myWidth)) 855 ->where($left . ' >= ' . $db->q($newSibLeft)); 856 $db->setQuery($query)->execute(); 857 858 // Move node and subnodes 859 $moveRight = $newSibLeft - $myLeft; 860 861 $query = $db->getQuery(true) 862 ->update($db->qn($this->getTableName())) 863 ->set($left . ' = ' . $db->q(0) . ' - ' . $left . ' + ' . $db->q($moveRight)) 864 ->set($right . ' = ' . $db->q(0) . ' - ' . $right . ' + ' . $db->q($moveRight)) 865 ->where($left . ' <= 0 - ' . $db->q($myLeft)) 866 ->where($right . ' >= 0 - ' . $db->q($myRight)); 867 $db->setQuery($query)->execute(); 868 869 // Commit the transaction 870 $db->transactionCommit(); 871 } 872 catch (\Exception $e) 873 { 874 $db->transactionRollback(); 875 876 throw $e; 877 } 878 879 // Let's load the record again to fetch the new values for lft and rgt 880 $this->load(); 881 882 return $this; 883 } 884 885 /** 886 * Moves the current node (and its subtree) to the right of another node. The other node can be in a different 887 * position in the tree or even under a different root. 888 * 889 * @param FOFTableNested $siblingNode 890 * 891 * @return $this for chaining 892 * 893 * @throws Exception 894 * @throws RuntimeException 895 */ 896 public function moveToRightOf(FOFTableNested $siblingNode) 897 { 898 // Sanity checks on current and sibling node position 899 if($this->lft >= $this->rgt) 900 { 901 throw new RuntimeException('Invalid position values for the current node'); 902 } 903 904 if($siblingNode->lft >= $siblingNode->rgt) 905 { 906 throw new RuntimeException('Invalid position values for the sibling node'); 907 } 908 909 $db = $this->getDbo(); 910 $left = $db->qn($this->getColumnAlias('lft')); 911 $right = $db->qn($this->getColumnAlias('rgt')); 912 913 // Get node metrics 914 $myLeft = $this->lft; 915 $myRight = $this->rgt; 916 $myWidth = $myRight - $myLeft + 1; 917 918 // Get parent metrics 919 $sibRight = $siblingNode->rgt; 920 921 // Start the transaction 922 $db->transactionStart(); 923 924 try 925 { 926 // Temporary remove subtree being moved 927 $query = $db->getQuery(true) 928 ->update($db->qn($this->getTableName())) 929 ->set("$left = " . $db->q(0) . " - $left") 930 ->set("$right = " . $db->q(0) . " - $right") 931 ->where($left . ' >= ' . $db->q($myLeft)) 932 ->where($right . ' <= ' . $db->q($myRight)); 933 $db->setQuery($query)->execute(); 934 935 // Close hole left behind 936 $query = $db->getQuery(true) 937 ->update($db->qn($this->getTableName())) 938 ->set($left . ' = ' . $left . ' - ' . $db->q($myWidth)) 939 ->where($left . ' > ' . $db->q($myRight)); 940 $db->setQuery($query)->execute(); 941 942 $query = $db->getQuery(true) 943 ->update($db->qn($this->getTableName())) 944 ->set($right . ' = ' . $right . ' - ' . $db->q($myWidth)) 945 ->where($right . ' > ' . $db->q($myRight)); 946 $db->setQuery($query)->execute(); 947 948 // Make a hole for the new items 949 $newSibRight = ($sibRight > $myRight) ? $sibRight - $myWidth : $sibRight; 950 951 $query = $db->getQuery(true) 952 ->update($db->qn($this->getTableName())) 953 ->set($left . ' = ' . $left . ' + ' . $db->q($myWidth)) 954 ->where($left . ' > ' . $db->q($newSibRight)); 955 $db->setQuery($query)->execute(); 956 957 $query = $db->getQuery(true) 958 ->update($db->qn($this->getTableName())) 959 ->set($right . ' = ' . $right . ' + ' . $db->q($myWidth)) 960 ->where($right . ' > ' . $db->q($newSibRight)); 961 $db->setQuery($query)->execute(); 962 963 // Move node and subnodes 964 $moveRight = ($sibRight > $myRight) ? $sibRight - $myRight : $sibRight - $myRight + $myWidth; 965 966 $query = $db->getQuery(true) 967 ->update($db->qn($this->getTableName())) 968 ->set($left . ' = ' . $db->q(0) . ' - ' . $left . ' + ' . $db->q($moveRight)) 969 ->set($right . ' = ' . $db->q(0) . ' - ' . $right . ' + ' . $db->q($moveRight)) 970 ->where($left . ' <= 0 - ' . $db->q($myLeft)) 971 ->where($right . ' >= 0 - ' . $db->q($myRight)); 972 $db->setQuery($query)->execute(); 973 974 // Commit the transaction 975 $db->transactionCommit(); 976 } 977 catch (\Exception $e) 978 { 979 $db->transactionRollback(); 980 981 throw $e; 982 } 983 984 // Let's load the record again to fetch the new values for lft and rgt 985 $this->load(); 986 987 return $this; 988 } 989 990 /** 991 * Alias for moveToRightOf 992 * 993 * @codeCoverageIgnore 994 * @param FOFTableNested $siblingNode 995 * 996 * @return $this for chaining 997 */ 998 public function makeNextSiblingOf(FOFTableNested $siblingNode) 999 { 1000 return $this->moveToRightOf($siblingNode); 1001 } 1002 1003 /** 1004 * Alias for makeNextSiblingOf 1005 * 1006 * @codeCoverageIgnore 1007 * @param FOFTableNested $siblingNode 1008 * 1009 * @return $this for chaining 1010 */ 1011 public function makeSiblingOf(FOFTableNested $siblingNode) 1012 { 1013 return $this->makeNextSiblingOf($siblingNode); 1014 } 1015 1016 /** 1017 * Alias for moveToLeftOf 1018 * 1019 * @codeCoverageIgnore 1020 * @param FOFTableNested $siblingNode 1021 * 1022 * @return $this for chaining 1023 */ 1024 public function makePreviousSiblingOf(FOFTableNested $siblingNode) 1025 { 1026 return $this->moveToLeftOf($siblingNode); 1027 } 1028 1029 /** 1030 * Moves a node and its subtree as a the first (leftmost) child of $parentNode 1031 * 1032 * @param FOFTableNested $parentNode 1033 * 1034 * @return $this for chaining 1035 * 1036 * @throws Exception 1037 */ 1038 public function makeFirstChildOf(FOFTableNested $parentNode) 1039 { 1040 // Sanity checks on current and sibling node position 1041 if($this->lft >= $this->rgt) 1042 { 1043 throw new RuntimeException('Invalid position values for the current node'); 1044 } 1045 1046 if($parentNode->lft >= $parentNode->rgt) 1047 { 1048 throw new RuntimeException('Invalid position values for the parent node'); 1049 } 1050 1051 $db = $this->getDbo(); 1052 $left = $db->qn($this->getColumnAlias('lft')); 1053 $right = $db->qn($this->getColumnAlias('rgt')); 1054 1055 // Get node metrics 1056 $myLeft = $this->lft; 1057 $myRight = $this->rgt; 1058 $myWidth = $myRight - $myLeft + 1; 1059 1060 // Get parent metrics 1061 $parentLeft = $parentNode->lft; 1062 1063 // Start the transaction 1064 $db->transactionStart(); 1065 1066 try 1067 { 1068 // Temporary remove subtree being moved 1069 $query = $db->getQuery(true) 1070 ->update($db->qn($this->getTableName())) 1071 ->set("$left = " . $db->q(0) . " - $left") 1072 ->set("$right = " . $db->q(0) . " - $right") 1073 ->where($left . ' >= ' . $db->q($myLeft)) 1074 ->where($right . ' <= ' . $db->q($myRight)); 1075 $db->setQuery($query)->execute(); 1076 1077 // Close hole left behind 1078 $query = $db->getQuery(true) 1079 ->update($db->qn($this->getTableName())) 1080 ->set($left . ' = ' . $left . ' - ' . $db->q($myWidth)) 1081 ->where($left . ' > ' . $db->q($myRight)); 1082 $db->setQuery($query)->execute(); 1083 1084 $query = $db->getQuery(true) 1085 ->update($db->qn($this->getTableName())) 1086 ->set($right . ' = ' . $right . ' - ' . $db->q($myWidth)) 1087 ->where($right . ' > ' . $db->q($myRight)); 1088 $db->setQuery($query)->execute(); 1089 1090 // Make a hole for the new items 1091 $newParentLeft = ($parentLeft > $myRight) ? $parentLeft - $myWidth : $parentLeft; 1092 1093 $query = $db->getQuery(true) 1094 ->update($db->qn($this->getTableName())) 1095 ->set($right . ' = ' . $right . ' + ' . $db->q($myWidth)) 1096 ->where($right . ' >= ' . $db->q($newParentLeft)); 1097 $db->setQuery($query)->execute(); 1098 1099 $query = $db->getQuery(true) 1100 ->update($db->qn($this->getTableName())) 1101 ->set($left . ' = ' . $left . ' + ' . $db->q($myWidth)) 1102 ->where($left . ' > ' . $db->q($newParentLeft)); 1103 $db->setQuery($query)->execute(); 1104 1105 // Move node and subnodes 1106 $moveRight = $newParentLeft - $myLeft + 1; 1107 1108 $query = $db->getQuery(true) 1109 ->update($db->qn($this->getTableName())) 1110 ->set($left . ' = ' . $db->q(0) . ' - ' . $left . ' + ' . $db->q($moveRight)) 1111 ->set($right . ' = ' . $db->q(0) . ' - ' . $right . ' + ' . $db->q($moveRight)) 1112 ->where($left . ' <= 0 - ' . $db->q($myLeft)) 1113 ->where($right . ' >= 0 - ' . $db->q($myRight)); 1114 $db->setQuery($query)->execute(); 1115 1116 // Commit the transaction 1117 $db->transactionCommit(); 1118 } 1119 catch (\Exception $e) 1120 { 1121 $db->transactionRollback(); 1122 1123 throw $e; 1124 } 1125 1126 // Let's load the record again to fetch the new values for lft and rgt 1127 $this->load(); 1128 1129 return $this; 1130 } 1131 1132 /** 1133 * Moves a node and its subtree as a the last (rightmost) child of $parentNode 1134 * 1135 * @param FOFTableNested $parentNode 1136 * 1137 * @return $this for chaining 1138 * 1139 * @throws Exception 1140 * @throws RuntimeException 1141 */ 1142 public function makeLastChildOf(FOFTableNested $parentNode) 1143 { 1144 // Sanity checks on current and sibling node position 1145 if($this->lft >= $this->rgt) 1146 { 1147 throw new RuntimeException('Invalid position values for the current node'); 1148 } 1149 1150 if($parentNode->lft >= $parentNode->rgt) 1151 { 1152 throw new RuntimeException('Invalid position values for the parent node'); 1153 } 1154 1155 $db = $this->getDbo(); 1156 $left = $db->qn($this->getColumnAlias('lft')); 1157 $right = $db->qn($this->getColumnAlias('rgt')); 1158 1159 // Get node metrics 1160 $myLeft = $this->lft; 1161 $myRight = $this->rgt; 1162 $myWidth = $myRight - $myLeft + 1; 1163 1164 // Get parent metrics 1165 $parentRight = $parentNode->rgt; 1166 1167 // Start the transaction 1168 $db->transactionStart(); 1169 1170 try 1171 { 1172 // Temporary remove subtree being moved 1173 $query = $db->getQuery(true) 1174 ->update($db->qn($this->getTableName())) 1175 ->set("$left = " . $db->q(0) . " - $left") 1176 ->set("$right = " . $db->q(0) . " - $right") 1177 ->where($left . ' >= ' . $db->q($myLeft)) 1178 ->where($right . ' <= ' . $db->q($myRight)); 1179 $db->setQuery($query)->execute(); 1180 1181 // Close hole left behind 1182 $query = $db->getQuery(true) 1183 ->update($db->qn($this->getTableName())) 1184 ->set($left . ' = ' . $left . ' - ' . $db->q($myWidth)) 1185 ->where($left . ' > ' . $db->q($myRight)); 1186 $db->setQuery($query)->execute(); 1187 1188 $query = $db->getQuery(true) 1189 ->update($db->qn($this->getTableName())) 1190 ->set($right . ' = ' . $right . ' - ' . $db->q($myWidth)) 1191 ->where($right . ' > ' . $db->q($myRight)); 1192 $db->setQuery($query)->execute(); 1193 1194 // Make a hole for the new items 1195 $newLeft = ($parentRight > $myRight) ? $parentRight - $myWidth : $parentRight; 1196 1197 $query = $db->getQuery(true) 1198 ->update($db->qn($this->getTableName())) 1199 ->set($left . ' = ' . $left . ' + ' . $db->q($myWidth)) 1200 ->where($left . ' >= ' . $db->q($newLeft)); 1201 $db->setQuery($query)->execute(); 1202 1203 $query = $db->getQuery(true) 1204 ->update($db->qn($this->getTableName())) 1205 ->set($right . ' = ' . $right . ' + ' . $db->q($myWidth)) 1206 ->where($right . ' >= ' . $db->q($newLeft)); 1207 $db->setQuery($query)->execute(); 1208 1209 // Move node and subnodes 1210 $moveRight = ($parentRight > $myRight) ? $parentRight - $myRight - 1 : $parentRight - $myRight - 1 + $myWidth; 1211 1212 $query = $db->getQuery(true) 1213 ->update($db->qn($this->getTableName())) 1214 ->set($left . ' = ' . $db->q(0) . ' - ' . $left . ' + ' . $db->q($moveRight)) 1215 ->set($right . ' = ' . $db->q(0) . ' - ' . $right . ' + ' . $db->q($moveRight)) 1216 ->where($left . ' <= 0 - ' . $db->q($myLeft)) 1217 ->where($right . ' >= 0 - ' . $db->q($myRight)); 1218 $db->setQuery($query)->execute(); 1219 1220 // Commit the transaction 1221 $db->transactionCommit(); 1222 } 1223 catch (\Exception $e) 1224 { 1225 $db->transactionRollback(); 1226 1227 throw $e; 1228 } 1229 1230 // Let's load the record again to fetch the new values for lft and rgt 1231 $this->load(); 1232 1233 return $this; 1234 } 1235 1236 /** 1237 * Alias for makeLastChildOf 1238 * 1239 * @codeCoverageIgnore 1240 * @param FOFTableNested $parentNode 1241 * 1242 * @return $this for chaining 1243 */ 1244 public function makeChildOf(FOFTableNested $parentNode) 1245 { 1246 return $this->makeLastChildOf($parentNode); 1247 } 1248 1249 /** 1250 * Makes the current node a root (and moving its entire subtree along the way). This is achieved by moving the node 1251 * to the right of its root node 1252 * 1253 * @return $this for chaining 1254 */ 1255 public function makeRoot() 1256 { 1257 // Make sure we are not a root 1258 if ($this->isRoot()) 1259 { 1260 return $this; 1261 } 1262 1263 // Get a reference to my root 1264 $myRoot = $this->getRoot(); 1265 1266 // Double check I am not a root 1267 if ($this->equals($myRoot)) 1268 { 1269 return $this; 1270 } 1271 1272 // Move myself to the right of my root 1273 $this->moveToRightOf($myRoot); 1274 $this->treeDepth = 0; 1275 1276 return $this; 1277 } 1278 1279 /** 1280 * Gets the level (depth) of this node in the tree. The result is cached in $this->treeDepth for faster retrieval. 1281 * 1282 * @throws RuntimeException 1283 * 1284 * @return int|mixed 1285 */ 1286 public function getLevel() 1287 { 1288 // Sanity checks on current node position 1289 if($this->lft >= $this->rgt) 1290 { 1291 throw new RuntimeException('Invalid position values for the current node'); 1292 } 1293 1294 if (is_null($this->treeDepth)) 1295 { 1296 $db = $this->getDbo(); 1297 1298 $fldLft = $db->qn($this->getColumnAlias('lft')); 1299 $fldRgt = $db->qn($this->getColumnAlias('rgt')); 1300 1301 $query = $db->getQuery(true) 1302 ->select('(COUNT(' . $db->qn('parent') . '.' . $fldLft . ') - 1) AS ' . $db->qn('depth')) 1303 ->from($db->qn($this->getTableName()) . ' AS ' . $db->qn('node')) 1304 ->join('CROSS', $db->qn($this->getTableName()) . ' AS ' . $db->qn('parent')) 1305 ->where($db->qn('node') . '.' . $fldLft . ' >= ' . $db->qn('parent') . '.' . $fldLft) 1306 ->where($db->qn('node') . '.' . $fldLft . ' <= ' . $db->qn('parent') . '.' . $fldRgt) 1307 ->where($db->qn('node') . '.' . $fldLft . ' = ' . $db->q($this->lft)) 1308 ->group($db->qn('node') . '.' . $fldLft) 1309 ->order($db->qn('node') . '.' . $fldLft . ' ASC'); 1310 1311 $this->treeDepth = $db->setQuery($query, 0, 1)->loadResult(); 1312 } 1313 1314 return $this->treeDepth; 1315 } 1316 1317 /** 1318 * Returns the immediate parent of the current node 1319 * 1320 * @throws RuntimeException 1321 * 1322 * @return FOFTableNested 1323 */ 1324 public function getParent() 1325 { 1326 // Sanity checks on current node position 1327 if($this->lft >= $this->rgt) 1328 { 1329 throw new RuntimeException('Invalid position values for the current node'); 1330 } 1331 1332 if ($this->isRoot()) 1333 { 1334 return $this; 1335 } 1336 1337 if (empty($this->treeParent) || !is_object($this->treeParent) || !($this->treeParent instanceof FOFTableNested)) 1338 { 1339 $db = $this->getDbo(); 1340 1341 $fldLft = $db->qn($this->getColumnAlias('lft')); 1342 $fldRgt = $db->qn($this->getColumnAlias('rgt')); 1343 1344 $query = $db->getQuery(true) 1345 ->select($db->qn('parent') . '.' . $fldLft) 1346 ->from($db->qn($this->getTableName()) . ' AS ' . $db->qn('node')) 1347 ->join('CROSS', $db->qn($this->getTableName()) . ' AS ' . $db->qn('parent')) 1348 ->where($db->qn('node') . '.' . $fldLft . ' > ' . $db->qn('parent') . '.' . $fldLft) 1349 ->where($db->qn('node') . '.' . $fldLft . ' <= ' . $db->qn('parent') . '.' . $fldRgt) 1350 ->where($db->qn('node') . '.' . $fldLft . ' = ' . $db->q($this->lft)) 1351 ->order($db->qn('parent') . '.' . $fldLft . ' DESC'); 1352 $targetLft = $db->setQuery($query, 0, 1)->loadResult(); 1353 1354 $table = $this->getClone(); 1355 $table->reset(); 1356 $this->treeParent = $table 1357 ->whereRaw($fldLft . ' = ' . $db->q($targetLft)) 1358 ->get()->current(); 1359 } 1360 1361 return $this->treeParent; 1362 } 1363 1364 /** 1365 * Is this a top-level root node? 1366 * 1367 * @return bool 1368 */ 1369 public function isRoot() 1370 { 1371 // If lft=1 it is necessarily a root node 1372 if ($this->lft == 1) 1373 { 1374 return true; 1375 } 1376 1377 // Otherwise make sure its level is 0 1378 return $this->getLevel() == 0; 1379 } 1380 1381 /** 1382 * Is this a leaf node (a node without children)? 1383 * 1384 * @throws RuntimeException 1385 * 1386 * @return bool 1387 */ 1388 public function isLeaf() 1389 { 1390 // Sanity checks on current node position 1391 if($this->lft >= $this->rgt) 1392 { 1393 throw new RuntimeException('Invalid position values for the current node'); 1394 } 1395 1396 return ($this->rgt - 1) == $this->lft; 1397 } 1398 1399 /** 1400 * Is this a child node (not root)? 1401 * 1402 * @codeCoverageIgnore 1403 * 1404 * @return bool 1405 */ 1406 public function isChild() 1407 { 1408 return !$this->isRoot(); 1409 } 1410 1411 /** 1412 * Returns true if we are a descendant of $otherNode 1413 * 1414 * @param FOFTableNested $otherNode 1415 * 1416 * @throws RuntimeException 1417 * 1418 * @return bool 1419 */ 1420 public function isDescendantOf(FOFTableNested $otherNode) 1421 { 1422 // Sanity checks on current node position 1423 if($this->lft >= $this->rgt) 1424 { 1425 throw new RuntimeException('Invalid position values for the current node'); 1426 } 1427 1428 if($otherNode->lft >= $otherNode->rgt) 1429 { 1430 throw new RuntimeException('Invalid position values for the other node'); 1431 } 1432 1433 return ($otherNode->lft < $this->lft) && ($otherNode->rgt > $this->rgt); 1434 } 1435 1436 /** 1437 * Returns true if $otherNode is ourselves or if we are a descendant of $otherNode 1438 * 1439 * @param FOFTableNested $otherNode 1440 * 1441 * @throws RuntimeException 1442 * 1443 * @return bool 1444 */ 1445 public function isSelfOrDescendantOf(FOFTableNested $otherNode) 1446 { 1447 // Sanity checks on current node position 1448 if($this->lft >= $this->rgt) 1449 { 1450 throw new RuntimeException('Invalid position values for the current node'); 1451 } 1452 1453 if($otherNode->lft >= $otherNode->rgt) 1454 { 1455 throw new RuntimeException('Invalid position values for the other node'); 1456 } 1457 1458 return ($otherNode->lft <= $this->lft) && ($otherNode->rgt >= $this->rgt); 1459 } 1460 1461 /** 1462 * Returns true if we are an ancestor of $otherNode 1463 * 1464 * @codeCoverageIgnore 1465 * @param FOFTableNested $otherNode 1466 * 1467 * @return bool 1468 */ 1469 public function isAncestorOf(FOFTableNested $otherNode) 1470 { 1471 return $otherNode->isDescendantOf($this); 1472 } 1473 1474 /** 1475 * Returns true if $otherNode is ourselves or we are an ancestor of $otherNode 1476 * 1477 * @codeCoverageIgnore 1478 * @param FOFTableNested $otherNode 1479 * 1480 * @return bool 1481 */ 1482 public function isSelfOrAncestorOf(FOFTableNested $otherNode) 1483 { 1484 return $otherNode->isSelfOrDescendantOf($this); 1485 } 1486 1487 /** 1488 * Is $node this very node? 1489 * 1490 * @param FOFTableNested $node 1491 * 1492 * @throws RuntimeException 1493 * 1494 * @return bool 1495 */ 1496 public function equals(FOFTableNested &$node) 1497 { 1498 // Sanity checks on current node position 1499 if($this->lft >= $this->rgt) 1500 { 1501 throw new RuntimeException('Invalid position values for the current node'); 1502 } 1503 1504 if($node->lft >= $node->rgt) 1505 { 1506 throw new RuntimeException('Invalid position values for the other node'); 1507 } 1508 1509 return ( 1510 ($this->getId() == $node->getId()) 1511 && ($this->lft == $node->lft) 1512 && ($this->rgt == $node->rgt) 1513 ); 1514 } 1515 1516 /** 1517 * Alias for isDescendantOf 1518 * 1519 * @codeCoverageIgnore 1520 * @param FOFTableNested $otherNode 1521 * 1522 * @return bool 1523 */ 1524 public function insideSubtree(FOFTableNested $otherNode) 1525 { 1526 return $this->isDescendantOf($otherNode); 1527 } 1528 1529 /** 1530 * Returns true if both this node and $otherNode are root, leaf or child (same tree scope) 1531 * 1532 * @param FOFTableNested $otherNode 1533 * 1534 * @return bool 1535 */ 1536 public function inSameScope(FOFTableNested $otherNode) 1537 { 1538 if ($this->isLeaf()) 1539 { 1540 return $otherNode->isLeaf(); 1541 } 1542 elseif ($this->isRoot()) 1543 { 1544 return $otherNode->isRoot(); 1545 } 1546 elseif ($this->isChild()) 1547 { 1548 return $otherNode->isChild(); 1549 } 1550 else 1551 { 1552 return false; 1553 } 1554 } 1555 1556 /** 1557 * get() will return all ancestor nodes and ourselves 1558 * 1559 * @return void 1560 */ 1561 protected function scopeAncestorsAndSelf() 1562 { 1563 $this->treeNestedGet = true; 1564 1565 $db = $this->getDbo(); 1566 1567 $fldLft = $db->qn($this->getColumnAlias('lft')); 1568 $fldRgt = $db->qn($this->getColumnAlias('rgt')); 1569 1570 $this->whereRaw($db->qn('parent') . '.' . $fldLft . ' >= ' . $db->qn('node') . '.' . $fldLft); 1571 $this->whereRaw($db->qn('parent') . '.' . $fldLft . ' <= ' . $db->qn('node') . '.' . $fldRgt); 1572 $this->whereRaw($db->qn('parent') . '.' . $fldLft . ' = ' . $db->q($this->lft)); 1573 } 1574 1575 /** 1576 * get() will return all ancestor nodes but not ourselves 1577 * 1578 * @return void 1579 */ 1580 protected function scopeAncestors() 1581 { 1582 $this->treeNestedGet = true; 1583 1584 $db = $this->getDbo(); 1585 1586 $fldLft = $db->qn($this->getColumnAlias('lft')); 1587 $fldRgt = $db->qn($this->getColumnAlias('rgt')); 1588 1589 $this->whereRaw($db->qn('parent') . '.' . $fldLft . ' > ' . $db->qn('node') . '.' . $fldLft); 1590 $this->whereRaw($db->qn('parent') . '.' . $fldLft . ' < ' . $db->qn('node') . '.' . $fldRgt); 1591 $this->whereRaw($db->qn('parent') . '.' . $fldLft . ' = ' . $db->q($this->lft)); 1592 } 1593 1594 /** 1595 * get() will return all sibling nodes and ourselves 1596 * 1597 * @return void 1598 */ 1599 protected function scopeSiblingsAndSelf() 1600 { 1601 $db = $this->getDbo(); 1602 1603 $fldLft = $db->qn($this->getColumnAlias('lft')); 1604 $fldRgt = $db->qn($this->getColumnAlias('rgt')); 1605 1606 $parent = $this->getParent(); 1607 $this->whereRaw($db->qn('node') . '.' . $fldLft . ' > ' . $db->q($parent->lft)); 1608 $this->whereRaw($db->qn('node') . '.' . $fldRgt . ' < ' . $db->q($parent->rgt)); 1609 } 1610 1611 /** 1612 * get() will return all sibling nodes but not ourselves 1613 * 1614 * @codeCoverageIgnore 1615 * 1616 * @return void 1617 */ 1618 protected function scopeSiblings() 1619 { 1620 $this->scopeSiblingsAndSelf(); 1621 $this->scopeWithoutSelf(); 1622 } 1623 1624 /** 1625 * get() will return only leaf nodes 1626 * 1627 * @return void 1628 */ 1629 protected function scopeLeaves() 1630 { 1631 $db = $this->getDbo(); 1632 1633 $fldLft = $db->qn($this->getColumnAlias('lft')); 1634 $fldRgt = $db->qn($this->getColumnAlias('rgt')); 1635 1636 $this->whereRaw($db->qn('node') . '.' . $fldLft . ' = ' . $db->qn('node') . '.' . $fldRgt . ' - ' . $db->q(1)); 1637 } 1638 1639 /** 1640 * get() will return all descendants (even subtrees of subtrees!) and ourselves 1641 * 1642 * @return void 1643 */ 1644 protected function scopeDescendantsAndSelf() 1645 { 1646 $this->treeNestedGet = true; 1647 1648 $db = $this->getDbo(); 1649 1650 $fldLft = $db->qn($this->getColumnAlias('lft')); 1651 $fldRgt = $db->qn($this->getColumnAlias('rgt')); 1652 1653 $this->whereRaw($db->qn('node') . '.' . $fldLft . ' >= ' . $db->qn('parent') . '.' . $fldLft); 1654 $this->whereRaw($db->qn('node') . '.' . $fldLft . ' <= ' . $db->qn('parent') . '.' . $fldRgt); 1655 $this->whereRaw($db->qn('parent') . '.' . $fldLft . ' = ' . $db->q($this->lft)); 1656 } 1657 1658 /** 1659 * get() will return all descendants (even subtrees of subtrees!) but not ourselves 1660 * 1661 * @return void 1662 */ 1663 protected function scopeDescendants() 1664 { 1665 $this->treeNestedGet = true; 1666 1667 $db = $this->getDbo(); 1668 1669 $fldLft = $db->qn($this->getColumnAlias('lft')); 1670 $fldRgt = $db->qn($this->getColumnAlias('rgt')); 1671 1672 $this->whereRaw($db->qn('node') . '.' . $fldLft . ' > ' . $db->qn('parent') . '.' . $fldLft); 1673 $this->whereRaw($db->qn('node') . '.' . $fldLft . ' < ' . $db->qn('parent') . '.' . $fldRgt); 1674 $this->whereRaw($db->qn('parent') . '.' . $fldLft . ' = ' . $db->q($this->lft)); 1675 } 1676 1677 /** 1678 * get() will only return immediate descendants (first level children) of the current node 1679 * 1680 * @return void 1681 */ 1682 protected function scopeImmediateDescendants() 1683 { 1684 // Sanity checks on current node position 1685 if($this->lft >= $this->rgt) 1686 { 1687 throw new RuntimeException('Invalid position values for the current node'); 1688 } 1689 1690 $db = $this->getDbo(); 1691 1692 $fldLft = $db->qn($this->getColumnAlias('lft')); 1693 $fldRgt = $db->qn($this->getColumnAlias('rgt')); 1694 1695 $subQuery = $db->getQuery(true) 1696 ->select(array( 1697 $db->qn('node') . '.' . $fldLft, 1698 '(COUNT(*) - 1) AS ' . $db->qn('depth') 1699 )) 1700 ->from($db->qn($this->getTableName()) . ' AS ' . $db->qn('node')) 1701 ->from($db->qn($this->getTableName()) . ' AS ' . $db->qn('parent')) 1702 ->where($db->qn('node') . '.' . $fldLft . ' >= ' . $db->qn('parent') . '.' . $fldLft) 1703 ->where($db->qn('node') . '.' . $fldLft . ' <= ' . $db->qn('parent') . '.' . $fldRgt) 1704 ->where($db->qn('node') . '.' . $fldLft . ' = ' . $db->q($this->lft)) 1705 ->group($db->qn('node') . '.' . $fldLft) 1706 ->order($db->qn('node') . '.' . $fldLft . ' ASC'); 1707 1708 $query = $db->getQuery(true) 1709 ->select(array( 1710 $db->qn('node') . '.' . $fldLft, 1711 '(COUNT(' . $db->qn('parent') . '.' . $fldLft . ') - (' . 1712 $db->qn('sub_tree') . '.' . $db->qn('depth') . ' + 1)) AS ' . $db->qn('depth') 1713 )) 1714 ->from($db->qn($this->getTableName()) . ' AS ' . $db->qn('node')) 1715 ->join('CROSS', $db->qn($this->getTableName()) . ' AS ' . $db->qn('parent')) 1716 ->join('CROSS', $db->qn($this->getTableName()) . ' AS ' . $db->qn('sub_parent')) 1717 ->join('CROSS', '(' . $subQuery . ') AS ' . $db->qn('sub_tree')) 1718 ->where($db->qn('node') . '.' . $fldLft . ' >= ' . $db->qn('parent') . '.' . $fldLft) 1719 ->where($db->qn('node') . '.' . $fldLft . ' <= ' . $db->qn('parent') . '.' . $fldRgt) 1720 ->where($db->qn('node') . '.' . $fldLft . ' >= ' . $db->qn('sub_parent') . '.' . $fldLft) 1721 ->where($db->qn('node') . '.' . $fldLft . ' <= ' . $db->qn('sub_parent') . '.' . $fldRgt) 1722 ->where($db->qn('sub_parent') . '.' . $fldLft . ' = ' . $db->qn('sub_tree') . '.' . $fldLft) 1723 ->group($db->qn('node') . '.' . $fldLft) 1724 ->having(array( 1725 $db->qn('depth') . ' > ' . $db->q(0), 1726 $db->qn('depth') . ' <= ' . $db->q(1), 1727 )) 1728 ->order($db->qn('node') . '.' . $fldLft . ' ASC'); 1729 1730 $leftValues = $db->setQuery($query)->loadColumn(); 1731 1732 if (empty($leftValues)) 1733 { 1734 $leftValues = array(0); 1735 } 1736 1737 array_walk($leftValues, function (&$item, $key) use (&$db) 1738 { 1739 $item = $db->q($item); 1740 }); 1741 1742 $this->whereRaw($db->qn('node') . '.' . $fldLft . ' IN (' . implode(',', $leftValues) . ')'); 1743 } 1744 1745 /** 1746 * get() will not return the selected node if it's part of the query results 1747 * 1748 * @param FOFTableNested $node The node to exclude from the results 1749 * 1750 * @return void 1751 */ 1752 public function withoutNode(FOFTableNested $node) 1753 { 1754 $db = $this->getDbo(); 1755 1756 $fldLft = $db->qn($this->getColumnAlias('lft')); 1757 1758 $this->whereRaw('NOT(' . $db->qn('node') . '.' . $fldLft . ' = ' . $db->q($node->lft) . ')'); 1759 } 1760 1761 /** 1762 * get() will not return ourselves if it's part of the query results 1763 * 1764 * @codeCoverageIgnore 1765 * 1766 * @return void 1767 */ 1768 protected function scopeWithoutSelf() 1769 { 1770 $this->withoutNode($this); 1771 } 1772 1773 /** 1774 * get() will not return our root if it's part of the query results 1775 * 1776 * @codeCoverageIgnore 1777 * 1778 * @return void 1779 */ 1780 protected function scopeWithoutRoot() 1781 { 1782 $rootNode = $this->getRoot(); 1783 $this->withoutNode($rootNode); 1784 } 1785 1786 /** 1787 * Returns the root node of the tree this node belongs to 1788 * 1789 * @return self 1790 * 1791 * @throws \RuntimeException 1792 */ 1793 public function getRoot() 1794 { 1795 // Sanity checks on current node position 1796 if($this->lft >= $this->rgt) 1797 { 1798 throw new RuntimeException('Invalid position values for the current node'); 1799 } 1800 1801 // If this is a root node return itself (there is no such thing as the root of a root node) 1802 if ($this->isRoot()) 1803 { 1804 return $this; 1805 } 1806 1807 if (empty($this->treeRoot) || !is_object($this->treeRoot) || !($this->treeRoot instanceof FOFTableNested)) 1808 { 1809 $this->treeRoot = null; 1810 1811 // First try to get the record with the minimum ID 1812 $db = $this->getDbo(); 1813 1814 $fldLft = $db->qn($this->getColumnAlias('lft')); 1815 $fldRgt = $db->qn($this->getColumnAlias('rgt')); 1816 1817 $subQuery = $db->getQuery(true) 1818 ->select('MIN(' . $fldLft . ')') 1819 ->from($db->qn($this->getTableName())); 1820 1821 try 1822 { 1823 $table = $this->getClone(); 1824 $table->reset(); 1825 $root = $table 1826 ->whereRaw($fldLft . ' = (' . (string)$subQuery . ')') 1827 ->get(0, 1)->current(); 1828 1829 if ($this->isDescendantOf($root)) 1830 { 1831 $this->treeRoot = $root; 1832 } 1833 } 1834 catch (\RuntimeException $e) 1835 { 1836 // If there is no root found throw an exception. Basically: your table is FUBAR. 1837 throw new \RuntimeException("No root found for table " . $this->getTableName() . ", node lft=" . $this->lft); 1838 } 1839 1840 // If the above method didn't work, get all roots and select the one with the appropriate lft/rgt values 1841 if (is_null($this->treeRoot)) 1842 { 1843 // Find the node with depth = 0, lft < our lft and rgt > our right. That's our root node. 1844 $query = $db->getQuery(true) 1845 ->select(array( 1846 $db->qn('node') . '.' . $fldLft, 1847 '(COUNT(' . $db->qn('parent') . '.' . $fldLft . ') - 1) AS ' . $db->qn('depth') 1848 )) 1849 ->from($db->qn($this->getTableName()) . ' AS ' . $db->qn('node')) 1850 ->join('CROSS', $db->qn($this->getTableName()) . ' AS ' . $db->qn('parent')) 1851 ->where($db->qn('node') . '.' . $fldLft . ' >= ' . $db->qn('parent') . '.' . $fldLft) 1852 ->where($db->qn('node') . '.' . $fldLft . ' <= ' . $db->qn('parent') . '.' . $fldRgt) 1853 ->where($db->qn('node') . '.' . $fldLft . ' < ' . $db->q($this->lft)) 1854 ->where($db->qn('node') . '.' . $fldRgt . ' > ' . $db->q($this->rgt)) 1855 ->having($db->qn('depth') . ' = ' . $db->q(0)) 1856 ->group($db->qn('node') . '.' . $fldLft); 1857 1858 // Get the lft value 1859 $targetLeft = $db->setQuery($query)->loadResult(); 1860 1861 if (empty($targetLeft)) 1862 { 1863 // If there is no root found throw an exception. Basically: your table is FUBAR. 1864 throw new \RuntimeException("No root found for table " . $this->getTableName() . ", node lft=" . $this->lft); 1865 } 1866 1867 try 1868 { 1869 $table = $this->getClone(); 1870 $table->reset(); 1871 $this->treeRoot = $table 1872 ->whereRaw($fldLft . ' = ' . $db->q($targetLeft)) 1873 ->get(0, 1)->current(); 1874 } 1875 catch (\RuntimeException $e) 1876 { 1877 // If there is no root found throw an exception. Basically: your table is FUBAR. 1878 throw new \RuntimeException("No root found for table " . $this->getTableName() . ", node lft=" . $this->lft); 1879 } 1880 } 1881 } 1882 1883 return $this->treeRoot; 1884 } 1885 1886 /** 1887 * Get all ancestors to this node and the node itself. In other words it gets the full path to the node and the node 1888 * itself. 1889 * 1890 * @codeCoverageIgnore 1891 * 1892 * @return FOFDatabaseIterator 1893 */ 1894 public function getAncestorsAndSelf() 1895 { 1896 $this->scopeAncestorsAndSelf(); 1897 1898 return $this->get(); 1899 } 1900 1901 /** 1902 * Get all ancestors to this node and the node itself, but not the root node. If you want to 1903 * 1904 * @codeCoverageIgnore 1905 * 1906 * @return FOFDatabaseIterator 1907 */ 1908 public function getAncestorsAndSelfWithoutRoot() 1909 { 1910 $this->scopeAncestorsAndSelf(); 1911 $this->scopeWithoutRoot(); 1912 1913 return $this->get(); 1914 } 1915 1916 /** 1917 * Get all ancestors to this node but not the node itself. In other words it gets the path to the node, without the 1918 * node itself. 1919 * 1920 * @codeCoverageIgnore 1921 * 1922 * @return FOFDatabaseIterator 1923 */ 1924 public function getAncestors() 1925 { 1926 $this->scopeAncestorsAndSelf(); 1927 $this->scopeWithoutSelf(); 1928 1929 return $this->get(); 1930 } 1931 1932 /** 1933 * Get all ancestors to this node but not the node itself and its root. 1934 * 1935 * @codeCoverageIgnore 1936 * 1937 * @return FOFDatabaseIterator 1938 */ 1939 public function getAncestorsWithoutRoot() 1940 { 1941 $this->scopeAncestors(); 1942 $this->scopeWithoutRoot(); 1943 1944 return $this->get(); 1945 } 1946 1947 /** 1948 * Get all sibling nodes, including ourselves 1949 * 1950 * @codeCoverageIgnore 1951 * 1952 * @return FOFDatabaseIterator 1953 */ 1954 public function getSiblingsAndSelf() 1955 { 1956 $this->scopeSiblingsAndSelf(); 1957 1958 return $this->get(); 1959 } 1960 1961 /** 1962 * Get all sibling nodes, except ourselves 1963 * 1964 * @codeCoverageIgnore 1965 * 1966 * @return FOFDatabaseIterator 1967 */ 1968 public function getSiblings() 1969 { 1970 $this->scopeSiblings(); 1971 1972 return $this->get(); 1973 } 1974 1975 /** 1976 * Get all leaf nodes in the tree. You may want to use the scopes to narrow down the search in a specific subtree or 1977 * path. 1978 * 1979 * @codeCoverageIgnore 1980 * 1981 * @return FOFDatabaseIterator 1982 */ 1983 public function getLeaves() 1984 { 1985 $this->scopeLeaves(); 1986 1987 return $this->get(); 1988 } 1989 1990 /** 1991 * Get all descendant (children) nodes and ourselves. 1992 * 1993 * Note: all descendant nodes, even descendants of our immediate descendants, will be returned. 1994 * 1995 * @codeCoverageIgnore 1996 * 1997 * @return FOFDatabaseIterator 1998 */ 1999 public function getDescendantsAndSelf() 2000 { 2001 $this->scopeDescendantsAndSelf(); 2002 2003 return $this->get(); 2004 } 2005 2006 /** 2007 * Get only our descendant (children) nodes, not ourselves. 2008 * 2009 * Note: all descendant nodes, even descendants of our immediate descendants, will be returned. 2010 * 2011 * @codeCoverageIgnore 2012 * 2013 * @return FOFDatabaseIterator 2014 */ 2015 public function getDescendants() 2016 { 2017 $this->scopeDescendants(); 2018 2019 return $this->get(); 2020 } 2021 2022 /** 2023 * Get the immediate descendants (children). Unlike getDescendants it only goes one level deep into the tree 2024 * structure. Descendants of descendant nodes will not be returned. 2025 * 2026 * @codeCoverageIgnore 2027 * 2028 * @return FOFDatabaseIterator 2029 */ 2030 public function getImmediateDescendants() 2031 { 2032 $this->scopeImmediateDescendants(); 2033 2034 return $this->get(); 2035 } 2036 2037 /** 2038 * Returns a hashed array where each element's key is the value of the $key column (default: the ID column of the 2039 * table) and its value is the value of the $column column (default: title). Each nesting level will have the value 2040 * of the $column column prefixed by a number of $separator strings, as many as its nesting level (depth). 2041 * 2042 * This is useful for creating HTML select elements showing the hierarchy in a human readable format. 2043 * 2044 * @param string $column 2045 * @param null $key 2046 * @param string $seperator 2047 * 2048 * @return array 2049 */ 2050 public function getNestedList($column = 'title', $key = null, $seperator = ' ') 2051 { 2052 $db = $this->getDbo(); 2053 2054 $fldLft = $db->qn($this->getColumnAlias('lft')); 2055 $fldRgt = $db->qn($this->getColumnAlias('rgt')); 2056 2057 if (empty($key) || !$this->hasField($key)) 2058 { 2059 $key = $this->getKeyName(); 2060 } 2061 2062 if (empty($column)) 2063 { 2064 $column = 'title'; 2065 } 2066 2067 $fldKey = $db->qn($this->getColumnAlias($key)); 2068 $fldColumn = $db->qn($this->getColumnAlias($column)); 2069 2070 $query = $db->getQuery(true) 2071 ->select(array( 2072 $db->qn('node') . '.' . $fldKey, 2073 $db->qn('node') . '.' . $fldColumn, 2074 '(COUNT(' . $db->qn('parent') . '.' . $fldKey . ') - 1) AS ' . $db->qn('depth') 2075 )) 2076 ->from($db->qn($this->getTableName()) . ' AS ' . $db->qn('node')) 2077 ->join('CROSS', $db->qn($this->getTableName()) . ' AS ' . $db->qn('parent')) 2078 ->where($db->qn('node') . '.' . $fldLft . ' >= ' . $db->qn('parent') . '.' . $fldLft) 2079 ->where($db->qn('node') . '.' . $fldLft . ' <= ' . $db->qn('parent') . '.' . $fldRgt) 2080 ->group($db->qn('node') . '.' . $fldLft) 2081 ->order($db->qn('node') . '.' . $fldLft . ' ASC'); 2082 2083 $tempResults = $db->setQuery($query)->loadAssocList(); 2084 $ret = array(); 2085 2086 if (!empty($tempResults)) 2087 { 2088 foreach ($tempResults as $row) 2089 { 2090 $ret[$row[$key]] = str_repeat($seperator, $row['depth']) . $row[$column]; 2091 } 2092 } 2093 2094 return $ret; 2095 } 2096 2097 /** 2098 * Locate a node from a given path, e.g. "/some/other/leaf" 2099 * 2100 * Notes: 2101 * - This will only work when you have a "slug" and a "hash" field in your table. 2102 * - If the path starts with "/" we will use the root with lft=1. Otherwise the first component of the path is 2103 * supposed to be the slug of the root node. 2104 * - If the root node is not found you'll get null as the return value 2105 * - You will also get null if any component of the path is not found 2106 * 2107 * @param string $path The path to locate 2108 * 2109 * @return FOFTableNested|null The found node or null if nothing is found 2110 */ 2111 public function findByPath($path) 2112 { 2113 // @todo 2114 } 2115 2116 public function isValid() 2117 { 2118 // @todo 2119 } 2120 2121 public function rebuild() 2122 { 2123 // @todo 2124 } 2125 2126 /** 2127 * Resets cached values used to speed up querying the tree 2128 * 2129 * @return static for chaining 2130 */ 2131 protected function resetTreeCache() 2132 { 2133 $this->treeDepth = null; 2134 $this->treeRoot = null; 2135 $this->treeParent = null; 2136 $this->treeNestedGet = false; 2137 2138 return $this; 2139 } 2140 2141 /** 2142 * Add custom, pre-compiled WHERE clauses for use in buildQuery. The raw WHERE clause you specify is added as is to 2143 * the query generated by buildQuery. You are responsible for quoting and escaping the field names and data found 2144 * inside the WHERE clause. 2145 * 2146 * @param string $rawWhereClause The raw WHERE clause to add 2147 * 2148 * @return $this For chaining 2149 */ 2150 public function whereRaw($rawWhereClause) 2151 { 2152 $this->whereClauses[] = $rawWhereClause; 2153 2154 return $this; 2155 } 2156 2157 /** 2158 * Builds the query for the get() method 2159 * 2160 * @return FOFDatabaseQuery 2161 */ 2162 protected function buildQuery() 2163 { 2164 $db = $this->getDbo(); 2165 2166 $query = $db->getQuery(true) 2167 ->select($db->qn('node') . '.*') 2168 ->from($db->qn($this->getTableName()) . ' AS ' . $db->qn('node')); 2169 2170 if ($this->treeNestedGet) 2171 { 2172 $query 2173 ->join('CROSS', $db->qn($this->getTableName()) . ' AS ' . $db->qn('parent')); 2174 } 2175 2176 // Apply custom WHERE clauses 2177 if (count($this->whereClauses)) 2178 { 2179 foreach ($this->whereClauses as $clause) 2180 { 2181 $query->where($clause); 2182 } 2183 } 2184 2185 return $query; 2186 } 2187 2188 /** 2189 * Returns a database iterator to retrieve records. Use the scope methods and the whereRaw method to define what 2190 * exactly will be returned. 2191 * 2192 * @param integer $limitstart How many items to skip from the start, only when $overrideLimits = true 2193 * @param integer $limit How many items to return, only when $overrideLimits = true 2194 * 2195 * @return FOFDatabaseIterator The data collection 2196 */ 2197 public function get($limitstart = 0, $limit = 0) 2198 { 2199 $limitstart = max($limitstart, 0); 2200 $limit = max($limit, 0); 2201 2202 $query = $this->buildQuery(); 2203 $db = $this->getDbo(); 2204 $db->setQuery($query, $limitstart, $limit); 2205 $cursor = $db->execute(); 2206 2207 $dataCollection = FOFDatabaseIterator::getIterator($db->name, $cursor, null, $this->config['_table_class']); 2208 2209 return $dataCollection; 2210 } 2211} 2212