List:General Discussion« Previous MessageNext Message »
From:Peter Brawley Date:January 29 2006 9:30pm
Subject:Re: display a hierarchic tree
View as plain text  
Jochen,

>...I want to display a tree like:
>
>	Software
>	   Virenscanner
>	     Linux
>               Debian Woody
>             Windows
>               Windowsd XP
>	   Packprogramm
>           Sniffer
>	Hardware
>	Vermischtes
>	....

It looks more like a parts explosion than a tree (ie it seems to have 
more than one root node). Perhaps you can hack this parts explosion 
example into the shape you need ...

DROP TABLE IF EXISTS bom;
CREATE TABLE bom (
  level SMALLINT,
  nodeID SMALLINT,
  parentID SMALLINT,
  qty DECIMAL(10,2),
  cost DECIMAL(10,2),
  leftedge SMALLINT,
  rightedge SMALLINT
);
DROP TABLE IF EXISTS edges;
CREATE TABLE edges LIKE tree;
DROP PROCEDURE IF EXISTS ShowComponents;
DELIMITER |

CREATE PROCEDURE ShowComponents( IN root INT )
BEGIN
  DECLARE thischild, thisparent, rows, maxrightedge INT DEFAULT 0;
  DECLARE thislevel, nextedgenum INT DEFAULT 1;
  DECLARE thisqty, thiscost DECIMAL(10,2);
  TRUNCATE edges;
  TRUNCATE bom;
  INSERT INTO edges
    SELECT childID,parentID FROM assemblies WHERE assemblyRoot = root;
  SET maxrightedge = 2 * (1 + (SELECT COUNT(*) FROM edges));
  INSERT INTO bom
    VALUES( thislevel, root, 0, 0, 0, nextedgenum, maxrightedge );
  SET nextedgenum = nextedgenum + 1;
  WHILE nextedgenum < maxrightedge DO
    -- How many children of this node remain in the edges table?
    SET rows = (
      SELECT COUNT(*)
      FROM bom AS s
      INNER JOIN edges AS t ON s.nodeID=t.parentID AND s.level=thislevel
    );
    IF rows > 0 THEN
      -- There is at least one child edge.
      -- Compute qty and cost, insert into bom, delete from edges.
      BEGIN
        -- Alas MySQL nulls MIN(t.childid) when we combine the next two 
queries
        SET thischild = (
          SELECT MIN(t.childID)
          FROM bom AS s
          INNER JOIN edges AS t ON s.nodeID=t.parentID AND s.level=thislevel
        );
        SET thisparent = (
          SELECT DISTINCT t.parentID
          FROM bom AS s
          INNER JOIN edges AS t ON s.nodeID=t.parentID AND s.level=thislevel
        );
        SET thisqty = (
          SELECT quantity FROM assemblies
          WHERE assemblyroot = root
            AND childID = thischild
            AND parentID = thisparent
        );
        SET thiscost = (
          SELECT a.assemblycost + (thisqty * (i.purchasecost + 
i.assemblycost ))
          FROM assemblies AS a
          INNER JOIN items AS i ON a.childID = i.itemID
          WHERE assemblyroot = root
            AND a.parentID = thisparent
            AND a.childID = thischild
        );
        INSERT INTO bom
          VALUES(thislevel+1, thischild, thisparent, thisqty, thiscost, 
nextedgenum, NULL);
        DELETE FROM edges
        WHERE childID = thischild AND parentID=thisparent;
        SET thislevel = thislevel + 1;
        SET nextedgenum = nextedgenum + 1;
      END;
    ELSE
      BEGIN
        -- Set rightedge, remove item from edges
        UPDATE bom
        SET rightedge=nextedgenum, level = -level
        WHERE level = thislevel;
        SET thislevel = thislevel - 1;
        SET nextedgenum = nextedgenum + 1;
      END;
    END IF;
  END WHILE;
  SET rows := ( SELECT COUNT(*) FROM edges );
  IF rows > 0 THEN
    SELECT 'Orphaned rows remain';
  ELSE
    -- Percolate qty values up the graph
    UPDATE bom AS c
      INNER JOIN bom AS p
      ON p.leftedge < c.leftedge
      AND p.rightedge > c.rightedge
      AND p.level = c.level + 1
      AND p.qty <> 1
      SET c.qty = c.qty * p.qty,
         c.cost = c.cost * p.qty;
    -- Total
    SET thiscost = (SELECT SUM(qty) FROM bom);
    UPDATE bom
    SET qty = 1, cost = thiscost
    WHERE nodeID = root;
    -- Show the result
    SELECT
      CONCAT(Space(Abs(level)*2),ItemName(nodeid)) AS Item,
      ROUND(qty,2) AS Qty,
      ROUND(cost, 2) AS Cost
    FROM bom
    ORDER BY leftedge;
  END IF;
END;
|
DELIMITER ;

PB

-----

Jochen Kaechelin wrote:
> I have the following table:
>
> mysql> select * from link_categories;
> +----+-------+-------------+---------------+-----------+---------+
> | id | level | category_id | category      | parent_id | deleted |
> +----+-------+-------------+---------------+-----------+---------+
> |  1 |     1 |        1000 | Software      |         0 |       0 |
> |  2 |     1 |        2000 | Harware       |         0 |       0 |
> |  3 |     2 |        1001 | Virenscanner  |      1000 |       0 |
> |  4 |     2 |        1003 | Packprogramme |      1000 |       0 |
> |  5 |     3 |        1004 | Linux         |      1001 |       0 |
> |  6 |     3 |        1005 | Windows       |      1001 |       0 |
> |  7 |     4 |        1006 | Windows XP    |      1005 |       0 |
> |  8 |     2 |        1007 | Sniffer       |      1000 |       0 |
> |  9 |     4 |        1008 | Debian Woody  |      1004 |       0 |
> | 10 |     1 |      100000 | Vermischtes   |         0 |       0 |
> +----+-------+-------------+---------------+-----------+---------+
> 10 rows in set (0.24 sec)
>
> and I want to display a tree like:
>
> 	Software
> 	   Virenscanner
> 	     Linux
>                Debian Woody
>              Windows
>                Windowsd XP
> 	   Packprogramm
>            Sniffer
> 	Hardware
> 	Vermischtes
> 	....
>
> Can someone give me hint how to build a query?
>
> I run MySQL 4.1.x and 5.0.x and I use PHP.
>
> Thanx.
>
>   


-- 
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 267.14.23/243 - Release Date: 1/27/2006

Thread
display a hierarchic treeJochen Kaechelin29 Jan
  • Re: display a hierarchic treePeter Brawley29 Jan
  • Re: display a hierarchic treemysql29 Jan
  • Re: display a hierarchic treeGleb Paharenko30 Jan
    • Finding the row number satisfying a conditon in a result setJacques Brignon30 Jan
      • Re: Finding the row number satisfying a conditon in a result setJake Peavy30 Jan
    • Re: display a hierarchic tree(Pete Harlan)30 Jan