MySQL function to return breadcrumb-like string from hierarchical structure

hierarchyMySQL

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.

Best Answer

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

Related Question