Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2015
    Posts
    2

    Unanswered: MYSQL Hierarchy Tree - Help needed

    Hello,

    I have these three hierarchy related tables in MYSQL.

    -- parent table
    CREATE TABLE `menu_parent` (
    `menu_id` int(11) NOT NULL, -- increment
    `parent_id` int(11) DEFAULT NULL,
    `porder` int(11) DEFAULT NULL,
    PRIMARY KEY (`menu_id`),
    KEY `parent_id` (`parent_id`) USING BTREE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8


    -- child table
    CREATE TABLE `menus_child` (
    `menu_child_id` int(11) NOT NULL AUTO_INCREMENT,
    `fk_menu_id` int(11) NOT NULL, -- foreign key (`menu_parent` table)
    `menu_name` varchar(128) NOT NULL,
    `menu_desc` varchar(500) DEFAULT NULL,
    PRIMARY KEY (`menu_child_id`),
    KEY `fk_menu_child_id` (`menu_child_id`) USING BTREE,
    CONSTRAINT `menu_child_ibfk_1` FOREIGN KEY (`fk_menu_id`) REFERENCES `menu_parent` (`menu_id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=720 DEFAULT CHARSET=utf8

    -- tree hierarchy table (closure table with the path length)
    CREATE TABLE `menus_tree_hierarchy` (
    `ancestor_id` int(11) NOT NULL,
    `descendant_id` int(11) NOT NULL,
    `pathlength` int(11) NOT NULL,
    PRIMARY KEY (`ancestor_id`,`descendant_id`),
    KEY `descendant_id` (`descendant_id`) USING BTREE,
    CONSTRAINT `menus_tree_hierarchy_fk_1` FOREIGN KEY (`ancestor_id`) REFERENCES `menu_parent` (`menu_id`),
    CONSTRAINT `menus_tree_hierarchy_fk_2` FOREIGN KEY (`descendant_id`) REFERENCES `menu_parent` (`menu_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8

    SELECT
    d.`menu_id`,
    d.`parent_id`,
    mnu_chld.`menu_name`,
    p.`pathlength`,
    GROUP_CONCAT(DISTINCT
    crumbs.`ancestor_id` SEPARATOR ','
    ) AS bread_crumbs
    FROM
    menu_parent AS d
    JOIN menus_child AS mnu_chld
    ON mnu_chld.fk_menu_id = d.menu_id
    JOIN menus_tree_hierarchy AS p
    ON d.menu_id = p.descendant_id
    JOIN menus_tree_hierarchy AS crumbs
    ON crumbs.`descendant_id` = p.`descendant_id`
    WHERE p.`ancestor_id` = 100 and
    d.menu_id = mnu_chld.fk_menu_id
    GROUP BY d.`menu_id`
    ORDER BY p.`descendant_id`;


    When the data is built newly it shows the result perfectly by using the above select query. But when we add any new data inbetween then it is showing in the last.

    For Example:

    "menu_id", "parent_id", "menu_name", "path_length", "bread_crumbs"
    100, 0, "Menu", 0, "100"
    101, 100,"VEHICLES",1, "101,100"
    102, 101,"Body Shop",2, "102,101,100"
    103, 101,"Dealers",2,"103,101,100"
    104, 101,"Repair Shop",2, "104,101,100"
    105, 100,"PROFESSIONALS",1,"105,100"
    106, 105,"Medical",2,"100,106,105"
    107, 106,"Surgeons",3,"107,100,106,105"
    108, 106,"Dentists",3,"105,108,100,106"
    109, 101,"Vehicle Paint Shop",2,"101,100,109"


    "Vehicle Paint Shop" should show after "Repair Shop" under "VEHICLES", but it is showing in the last. I tried with different options in the order by clause in the above query but it is not working (showing query results as expected).

    Someone had suggested

    If max possible tree depth is known to be N, a query with N-1 joins from menu_id to parent_id can traverse the tree; otherwise you need recursive logic, available in MySQL only in stored routines, see http://www.artfulsoftware.com/mysqlb...qled1ch20.html eg listing 7 et seq.

    But I am still unable to write a N - 1 query.

    My problem is somewhat similar to what is mentioned in the below link

    http://stackoverflow.com/questions/8...n-out-in-the-c


    This only works if my tree was created sequentially. Imagine your root item wasn't 1, it was 12, this would no longer work. Move a couple nodes around here to remove the sequence and this printing will break. Anybody have another solution?

    Someone had replied as "If your closure table also includes a pathlength column you can use GROUP_CONCAT(crumbs.ancestor ORDER BY pathlength)". But inspite of using the path length in
    the order by clause within group_concat I did not get the hierarchy in the proper order.

    There is some solution in the below link

    http://explainextended.com/2009/03/1...cestry-chains/

    They have suggested a function "hierarchy_sys_connect_by_path". This function will work even without initializing the session variables. In this case it will return the full ancestry chain path up to the first orphan node.
    If @start_with is initialized, the function will return the ancestry chain up to @start_with row.

    Though the function works, it is taking almost double time than what it used to before. So it may not be approved.

    I am stuck. After writing the stored procedures and triggers based on the closure table model, I found that my tree hierarchy does not show the hierarchy in the proper order if I add any new node inbetween.

    Please advice.

    Thanks & Regards
    Shreya

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    an alternative approach can be found here...
    http://sqllessons.com/categories.html

    history tends to suggest that its unusual to go down more than 6, possibly 8 levels

  3. #3
    Join Date
    Dec 2015
    Posts
    2
    But in my case, depth of the nodes is not known. Unless we know the depth or level of the menus we cannot use the "adjacency model".



    Thanks and regards
    Shreya

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    So do some basic research, establish how mang levels you need. Id be surprised if it is more than 6. The adjacency model works fine, the 'limit' comes in when you structure the query. There is nothing stopping you implementing the query with, say, 10 or more levels. There is nothing stoppibg you retrieving segments of the tree by say specifying the parent and requesting all dependant rows.

    Even on complex engineering assemblies its rare to go beyond 5 levels. The biological animal kingdom is 7 arguably 9 levels.
    I'd rather be riding on the Tiger 800 or the Norton

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •