EdgeListToNestedSet

EdgeListToNestedSet.sql

DELIMITER ;;

DROP PROCEDURE IF EXISTS EdgeListToNestedSet;;

CREATE PROCEDURE EdgeListToNestedSet( edgeTable CHAR(64), idCol CHAR(64), parentCol CHAR(64) )
BEGIN
  DECLARE maxrightedge, rows INTEGER DEFAULT 0;
  DECLARE trees, current INTEGER DEFAULT 1;
  DECLARE nextedge INTEGER DEFAULT 2;
  DECLARE msg CHAR(128);
  -- create working tree table as a copy of edgeTable
  DROP TEMPORARY TABLE IF EXISTS tree;
  CREATE TEMPORARY TABLE tree( childID INT, parentID INT );
  SET @sql = CONCAT( 'INSERT INTO tree SELECT ', idCol, ',', parentCol, ' FROM ', edgeTable );
  PREPARE stmt FROM @sql; EXECUTE stmt; DROP PREPARE stmt;
  -- initialise result table
  DROP TABLE IF EXISTS nestedsettree;
  CREATE TABLE nestedsettree (
    top INTEGER, nodeID INTEGER, leftedge INTEGER, rightedge INTEGER,
    KEY(nodeID,leftedge,rightedge)
  ) ENGINE=HEAP;
  -- root is child with NULL parent or parent which is not a child
  SET @nulls = ( SELECT Count(*) FROM tree WHERE parentID IS NULL );
  IF @nulls>1 THEN SET trees=2;
  ELSEIF @nulls=1 THEN
    SET @root = ( SELECT childID FROM tree WHERE parentID IS NULL );
    DELETE FROM tree WHERE childID=@root;
  ELSE
    SET @sql = CONCAT( 'SELECT Count(DISTINCT f.', parentcol, ') INTO @roots FROM ', edgeTable,
                       ' f LEFT JOIN ', edgeTable, ' t ON f.', parentCol, '=', 't.', idCol,
                       ' WHERE t.', idCol, ' IS NULL' );
    PREPARE stmt FROM @sql; EXECUTE stmt; DROP PREPARE stmt;
    IF @roots <> 1 THEN SET trees=@roots;
    ELSE
      SET @sql = CONCAT( 'SELECT DISTINCT f.', parentCol, ' INTO @root FROM ', edgeTable,
                         ' f LEFT JOIN ', edgeTable, ' t ON f.', parentCol, '=', 't.',
                         idCol, ' WHERE t.', idCol, ' IS NULL' );
      PREPARE stmt FROM @sql; EXECUTE stmt; DROP PREPARE stmt;
    END IF;
  END IF;
  IF trees<>1 THEN
    SET msg = IF( trees=0, "No tree found", "Table has multiple trees" );
    SELECT msg AS 'Cannot continue';
  ELSE    -- build nested sets tree
    SET maxrightedge = 2 * (1 + (SELECT + COUNT(*) FROM tree));
    INSERT INTO nestedsettree VALUES( 1, @root, 1, maxrightedge );
    WHILE nextedge < maxrightedge DO
      SET rows=(SELECT Count(*) FROM nestedsettree s JOIN tree t ON s.nodeID=t.parentID AND s.top=current);
      IF rows > 0 THEN
        BEGIN
          INSERT INTO nestedsettree
            SELECT current+1, MIN(t.childID), nextedge, NULL
            FROM nestedsettree AS s
            JOIN tree AS t ON s.nodeID = t.parentID AND s.top = current;
          DELETE FROM tree
          WHERE childID = (SELECT nodeID FROM nestedsettree WHERE top=(current+1));
          SET nextedge = nextedge + 1, current = current + 1;
         END;
       ELSE
         UPDATE nestedsettree SET rightedge=nextedge, top = -top WHERE top=current;
         SET nextedge=nextedge+1, current=current-1;
      END IF;
    END WHILE;
    -- show result
    IF (SELECT COUNT(*) FROM tree) > 0 THEN
      SELECT 'Orphaned rows remain' AS 'Error';
    END IF;
    DROP TEMPORARY TABLE tree;
  END IF;
END;;

DELIMITER ;

CALL EdgeListToNestedSet( 'ps_category', 'id_category', 'id_parent');

UPDATE
		ps_category c
	SET
		c.level_depth = (SELECT ABS(n.top)-2 FROM nestedsettree n WHERE n.nodeId = c.id_category),
		c.nleft = (SELECT n.leftedge -1 FROM nestedsettree n WHERE n.nodeId = c.id_category),
		c.nright = (SELECT n.rightedge - 1 FROM nestedsettree n WHERE n.nodeId = c.id_category);