3

Show create table:

CREATE TABLE `a` (
  `id` int(11) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `parent_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `parent_id` (`parent_id`),
  CONSTRAINT `a_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `a` (`id`) ON DELETE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Select:

+----+---------+-----------+
| id | name    | parent_id |
+----+---------+-----------+
|  1 | World   |         1 |
|  2 | Europe  |         1 |
|  3 | USA     |         1 |
|  4 | France  |         2 |
|  5 | Germany |         2 |
+----+---------+-----------+

Question:

  • how do I best define a function which, passed id 4 as an argument, returns:

    "World > Europe > France"

This hierarchical "nesting" by "parent_id" could be arbitrarily deep, not just 3 levels deep.

4
  • 2
    Don't. It's not the databases job to do formatting like that. Help the developers make sure the data is modeled properly and let them pass the retrieved structures on to the UI/UX people to format it as need be.
    – atxdba
    Jun 12 '12 at 4:34
  • It would still be educational for me to know how would one write one such function on the database... even if you discourage the approach. Jun 12 '12 at 5:03
  • If you insist on storing graph this way, read artfulsoftware.com/mysqlbook/sampler/mysqled1ch20.html . It has nice examples how traverse such structures effectively.
    – a1ex07
    Jun 12 '12 at 14:30
  • I'm interested in doing the inverse... given World > Europe > France return 4
    – Brad Kent
    Sep 27 '17 at 19:14
0

You can use the following function for returning Hierarchical structure for N no:of leaf nodes like "World > Europe > France > S1 > S2"

DELIMITER $$
 
DROP FUNCTION IF EXISTS `test`.`test` $$ 
CREATE FUNCTION `test`.`test`(V_Argument_Parent_ID integer) 
RETURNS varchar(1024) No
SQL BEGIN

  Declare Done Integer default 0;   
  Declare V_id Integer default 0;  
  Declare V_parent_id Integer default 0;

  Declare V_name varchar(1024) default '';   
  Declare V_Result varchar(1024) default '';   
  Declare V_TempResult varchar(1024) default '';

  Declare continue handler for SQLWARNING set Done = 1;   
  Declare continue handler for NOT FOUND set Done = 1;   
  DECLARE EXIT HANDLER FOR 1001 set Done = 1;

  SELECT id, name, parent_id into V_id, V_name, V_parent_id 
  FROM a
  where ID = V_Argument_Parent_ID;

  IF V_parent_id = V_id then
    Set V_Result = V_name;   Else
    Set V_TempResult = ''; 
    Set V_TempResult = concat_ws('',V_TempResult,'->',V_name);
 
    MainLoop: Loop

     set V_Argument_Parent_ID = V_parent_id;
     SELECT id, name, parent_id into V_id, V_name, V_parent_id 
     FROM a where ID = V_Argument_Parent_ID;

     Set V_TempResult = concat_ws('',V_TempResult,'->',V_name);
     IF V_parent_id = V_id then
         Leave MainLoop;
     End IF;

    End Loop MainLoop;

    Set V_Result = V_TempResult;

    set V_Result = trim(both '->' from V_Result);
 
  End IF;
 
  Return V_Result;
 
 
END $$
 
DELIMITER ;

Try out

1
  • Spot on! You just taught me how to write the function! Jun 20 '12 at 22:04
0
delimiter //
create function derp(IN myId int) returns varchar(255) begin
    select concat(t1.name, ' > ', t2.name, ' > ' t3.name) from a t1 join a t2 on t1.parent_id=t2.id join a t3 on t2.parent_id = t3.id where t1.id=myId;
end//

Not tested, just off the top of my head. For reference http://dev.mysql.com/doc/refman/5.5/en/create-procedure.html

3
  • This "nesting" by "parent_id" could be arbitrarily deep, not just 3 levels deep. Jun 12 '12 at 5:34
  • 1
    All the more reason to leave the recursion to the application layer
    – atxdba
    Jun 12 '12 at 5:37
  • If you had to teach me how to code this recursion, how would you teach me? (suspending your judgment, just sticking to the code) Jun 12 '12 at 6:05
0

Now, inspired by Phanindra's teachings whose principles I tried to follow, I wrote what seems to me a bit "easier on the eyes" rephrasing of his correct answer. Here for posterity. Now the question: was it really such a bad idea to write a function like this, as per the comments? And if so, why?

DELIMITER $$

DROP FUNCTION IF EXISTS places.get_breadcrumb_string $$

CREATE FUNCTION places.get_breadcrumb_string
    (LEAF_ID INTEGER)
        RETURNS VARCHAR(1024)

    BEGIN

        DECLARE HIERARCHY_SEPARATOR VARCHAR(255);  # Hierarchy step-down marker
        DECLARE BREADCRUMB_STRING   VARCHAR(1024); # Value to return, will take the form "Node -separator- Node ..."

        DECLARE V_PLACE_ID          INTEGER;
        DECLARE V_PLACE_NAME        VARCHAR(255);
        DECLARE V_PARENT_PLACE_ID   INTEGER;

        DECLARE ID_TO_LOOKUP_NEXT   INTEGER;

        SET HIERARCHY_SEPARATOR   = ' > ';         # Intended format: "Node > Node > Node"
        SET BREADCRUMB_STRING     = '';

        SET ID_TO_LOOKUP_NEXT     = LEAF_ID;

        LoopingLookUp: LOOP

            SELECT
                `id`,
                `name`,
                `parent_id`
            INTO
                V_PLACE_ID,
                V_PLACE_NAME,
                V_PARENT_PLACE_ID
            FROM
                `places`
            WHERE
                `_id` = ID_TO_LOOKUP_NEXT
            ;

            SET BREADCRUMB_STRING = CONCAT(
                HIERARCHY_SEPARATOR,
                V_PLACE_NAME,
                BREADCRUMB_STRING
            );

            IF V_PARENT_PLACE_ID = V_PLACE_ID THEN
                LEAVE LoopingLookUp;
            END IF;

            SET ID_TO_LOOKUP_NEXT = V_PARENT_PLACE_ID;

        END LOOP;

        SET BREADCRUMB_STRING = TRIM(LEADING HIERARCHY_SEPARATOR FROM BREADCRUMB_STRING);

        RETURN BREADCRUMB_STRING;

END $$

DELIMITER ;
1
  • This function is greatly made, but take in mind that if you use null termination of the parent list it creates infinite loop. It should be great if you implement a max loop protection. Apr 26 '17 at 20:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.