Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
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. –  Robottinosino 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

3 Answers 3

up vote 0 down vote accepted

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

share|improve this answer
    
Spot on! You just taught me how to write the function! –  Robottinosino Jun 20 '12 at 22:04
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

share|improve this answer
    
This "nesting" by "parent_id" could be arbitrarily deep, not just 3 levels deep. –  Robottinosino 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) –  Robottinosino Jun 12 '12 at 6:05

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 ;
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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