[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1813
  • Last Modified:

How do I fix lft and rgt in mosets tree?

Hi,

I am using Mosets Tree 2.2.0 in Joomla 1.6.1.

I find that from time to time, the nested model left and right indexes bleed into each other and cross boundaries they shouldn't, I have tried recounting the category indexes which does not fix the issue.

I can easily fix the issue by manually resetting the the left and right indexes in MySQL so that they match the correct tree structure, but, we have allot of categories and I don't want to be doing this every time as it is time consuming. So my questions are:

1) Why does this happen?
2) How can I prevent it from happening?
3) How can I fix it quickly when it happens? (I could write an automated query that fixes lft and rgt to run daily, hopefully the answer is easier than that)

Reference:
http://en.wikipedia.org/wiki/Nested_set_model
0
jwleys
Asked:
jwleys
  • 7
1 Solution
 
jwleysAuthor Commented:
I found this, which may present me with a method for fixing quickly. Potentially I could write a scheduled event in MySQL which repairs the nested set model left and right indexes.

Reference MySQL scheduled event documentation:
http://dev.mysql.com/doc/refman/5.1/en/create-event.html

Reference repairing nested set model left and right:
stackoverflow.com/questions/889527/move-node-in-nested-set
0
 
jwleysAuthor Commented:
another good resource for repairing nested set model left and right:
stackoverflow.com/questions/3623645/how-to-repair-a-corrupted-mptt-tree-nested-set-in-the-database-using-sql
0
 
jwleysAuthor Commented:
Found this function in administrator/com_mtree/admin.mtree.class.php

function updateLftRgt() //trying to troubleshoot it:

function updateLftRgt() {
		
		# Retrieve parent's rgt value
		$this->_db->setQuery( "SELECT rgt FROM #__mt_cats WHERE cat_id = $this->cat_parent LIMIT 1" );
		$parent_rgt = $this->_db->loadResult();
		
		if ( is_null($parent_rgt) && $this->cat_parent == 0 ) {
			$this->_db->setQuery( "SELECT rgt FROM #__mt_cats WHERE cat_parent = -1 LIMIT 1" );
			$parent_rgt = $this->_db->loadResult();

			if ( is_null($parent_rgt) ) {
				$this->_db->setQuery( "SELECT rgt FROM #__mt_cats WHERE cat_name = 'Root' LIMIT 1" );
				$parent_rgt = $this->_db->loadResult();

				if ( is_null($parent_rgt) ) {
					$this->_db->setQuery( "SELECT (MAX(rgt) +1) FROM #__mt_cats" );
					$parent_rgt = $this->_db->loadResult();
				}

			}

		}

		# Update all category's lft and rgt (+ 2) to the right of this category
		$this->_db->setQuery("UPDATE #__mt_cats SET lft = lft+2 WHERE lft >= $parent_rgt");
		if (!$this->_db->query()) {
				echo "<script> alert('".$this->_db->getErrorMsg()."'); window.history.go(-1); </script>\n";
				return false;
		}

		$this->_db->setQuery("UPDATE #__mt_cats SET rgt = rgt+2 WHERE rgt >= $parent_rgt");
		if (!$this->_db->query()) {
				echo "<script> alert('".$this->_db->getErrorMsg()."'); window.history.go(-1); </script>\n";
				return false;
		}

		# Update this category's lft and rgt
		$this->_db->setQuery("UPDATE #__mt_cats SET lft = ".$parent_rgt.", rgt = ".($parent_rgt+1)." WHERE cat_id = $this->cat_id");
		if (!$this->_db->query()) {
				echo "<script> alert('".$this->_db->getErrorMsg()."'); window.history.go(-1); </script>\n";
				return false;
		}
		$this->lft = $parent_rgt;
		$this->rgt = $parent_rgt+1;

		return true;

	}

Open in new window

0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
jwleysAuthor Commented:
I wrote a query for checking the current status of the nested set model which I have found helpful for troubleshooting:

SELECT
p.cat_parent as pparent, p.cat_id as pid, p.cat_name as pname, p.lft as plft, p.rgt as prgt,
c.cat_parent as cparent, c.cat_id as cid, c.cat_name as cname, c.lft as clft, c.rgt as crgt  
FROM
  health3_mt_cats as c
LEFT JOIN
  health3_mt_cats as p
ON
  p.lft=(
    SELECT
      MAX(lft)
    FROM
      health3_mt_cats AS l
    WHERE
      c.lft > l.lft
      AND c.lft < l.rgt
  )

Open in new window

0
 
jwleysAuthor Commented:
I have adapted the query from (http://stackoverflow.com/questions/3623645/how-to-repair-a-corrupted-mptt-tree-nested-set-in-the-database-using-sql) to work with Mosets Tree. I will report back once I have tested it tonight to see if it works:

DROP PROCEDURE IF EXISTS tree_recover;

DELIMITER //

CREATE PROCEDURE tree_recover ()
MODIFIES SQL DATA
BEGIN

    DECLARE currentId, currentParentId  CHAR(36);
    DECLARE currentLeft                 INT;
    DECLARE startId                     INT DEFAULT 1;

    # Determines the max size for MEMORY tables.
    SET max_heap_table_size = 1024 * 1024 * 512;

    START TRANSACTION;

    # Temporary MEMORY table to do all the heavy lifting in,
    # otherwise performance is simply abysmal.
    CREATE TABLE `health4_tmp_tree` (
        `cat_id`        char(36) NOT NULL DEFAULT '',
        `cat_parent` char(36)          DEFAULT NULL,
        `lft`       int(11)  unsigned DEFAULT NULL,
        `rgt`      int(11)  unsigned DEFAULT NULL,
        PRIMARY KEY      (`cat_id`),
        INDEX USING HASH (`cat_parent`),
        INDEX USING HASH (`lft`),
        INDEX USING HASH (`rgt`)
    ) ENGINE = MEMORY
    SELECT `cat_id`,
           `cat_parent`,
           `lft`,
           `rgt`
    FROM   `health4_mt_cats`;

    # Leveling the playing field.
    UPDATE  `health4_tmp_tree`
    SET     `lft`  = NULL,
            `rgt` = NULL;

    # Establishing starting numbers for all root elements.
    WHILE EXISTS (SELECT * FROM `health4_tmp_tree` WHERE `cat_parent` IS NULL AND `lft` IS NULL AND `rgt` IS NULL LIMIT 1) DO

        UPDATE `health4_tmp_tree`
        SET    `lft`  = startId,
               `rgt` = startId + 1
        WHERE  `cat_parent` IS NULL
          AND  `lft`       IS NULL
          AND  `rgt`      IS NULL
        LIMIT  1;

        SET startId = startId + 2;

    END WHILE;

    # Switching the indexes for the lft/rgt columns to B-Trees to speed up the next section, which uses range queries.
    DROP INDEX `lft`  ON `health4_tmp_tree`;
    DROP INDEX `rgt` ON `health4_tmp_tree`;
    CREATE INDEX `lft`  USING BTREE ON `health4_tmp_tree` (`lft`);
    CREATE INDEX `rgt` USING BTREE ON `health4_tmp_tree` (`rgt`);

    # Numbering all child elements
    WHILE EXISTS (SELECT * FROM `health4_tmp_tree` WHERE `lft` IS NULL LIMIT 1) DO

        # Picking an unprocessed element which has a processed parent.
        SELECT     `health4_tmp_tree`.`cat_id`
          INTO     currentId
        FROM       `health4_tmp_tree`
        INNER JOIN `health4_tmp_tree` AS `parents`
                ON `health4_tmp_tree`.`cat_parent` = `parents`.`cat_id`
        WHERE      `health4_tmp_tree`.`lft` IS NULL
          AND      `parents`.`lft`  IS NOT NULL
        LIMIT      1;

        # Finding the element's parent.
        SELECT  `cat_parent`
          INTO  currentParentId
        FROM    `health4_tmp_tree`
        WHERE   `cat_id` = currentId;

        # Finding the parent's lft value.
        SELECT  `lft`
          INTO  currentLeft
        FROM    `health4_tmp_tree`
        WHERE   `cat_id` = currentParentId;

        # Shifting all elements to the right of the current element 2 to the right.
        UPDATE `health4_tmp_tree`
        SET    `rgt` = `rgt` + 2
        WHERE  `rgt` > currentLeft;

        UPDATE `health4_tmp_tree`
        SET    `lft` = `lft` + 2
        WHERE  `lft` > currentLeft;

        # Setting lft and rgt values for current element.
        UPDATE `health4_tmp_tree`
        SET    `lft`  = currentLeft + 1,
               `rgt` = currentLeft + 2
        WHERE  `cat_id`   = currentId;

    END WHILE;

    # Writing calculated values back to physical table.
    UPDATE `health4_mt_cats`, `health4_tmp_tree`
    SET    `health4_mt_cats`.`lft`  = `health4_tmp_tree`.`lft`,
           `health4_mt_cats`.`rgt` = `health4_tmp_tree`.`rgt`
    WHERE  `health4_mt_cats`.`cat_id`   = `health4_tmp_tree`.`cat_id`;

    COMMIT;

    DROP TABLE `health4_tmp_tree`;

END//

DELIMITER ;

Open in new window

0
 
jwleysAuthor Commented:
This works like a charm
0
 
jwleysAuthor Commented:
Although the above works, Mosets Tree have their own in-built solution for fixing this, I would suggest you use it rather than my method:

http://www.example.com/administrator/index.php?option=com_mtree&task=rebuild_tree 

Reference:
http://www.mosets.com/help/kb/mosets-tree/27
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now