Link to home
Start Free TrialLog in
Avatar of Jeremy Leys
Jeremy LeysFlag for New Zealand

asked on

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
Avatar of Jeremy Leys
Jeremy Leys
Flag of New Zealand image

ASKER

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
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
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

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

ASKER CERTIFIED SOLUTION
Avatar of Jeremy Leys
Jeremy Leys
Flag of New Zealand image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
This works like a charm
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