MySQL: Using a stored function to flatten a hierarchical path

[ATC]

MySQL does not allow stored functions to call themselves recursively, so how does one flatten a hierarchical path with an unknown number of levels?

[ATC]

Keep in mind that any recursive algorithm can be rewritten as a loop (and vice versa):


select * from folders;
+----+-----------+--------+
| id | name      | parent |
+----+-----------+--------+
|  1 | docs      |      0 |
|  2 | old       |      1 |
|  3 | new       |      1 |
|  4 | deep      |      3 |
|  5 | very deep |      4 |
+----+-----------+--------+
5 rows in set (0.00 sec)


delimiter !!

create function foldername (this_id integer)
returns varchar(255)
deterministic
reads sql data
begin
  declare result, this_name varchar(255) default '';
  declare parent_id integer;
  set parent_id = this_id;

  while this_id is not null
  do
    set parent_id = null;

    select name, parent
    into this_name, parent_id
    from folders where id = this_id;

    set this_id = parent_id;
    if parent_id is not null
    then
      set result = concat(this_name, '/', result);
    end if;
  end while;

  return result;
end;
!!

delimiter ;

select *,foldername(id) from folders;
+----+-----------+--------+--------------------------+
| id | name      | parent | foldername(id)           |
+----+-----------+--------+--------------------------+
|  1 | docs      |      0 | docs/                    |
|  2 | old       |      1 | docs/old/                |
|  3 | new       |      1 | docs/new/                |
|  4 | deep      |      3 | docs/new/deep/           |
|  5 | very deep |      4 | docs/new/deep/very deep/ |
+----+-----------+--------+--------------------------+
5 rows in set, 5 warnings (0.01 sec)


Use caution: This function will loop endlessly if your data contains a reference loop. Either make sure your data is consistent, or add loop handling code.

[Floyd-ATC]

Here's a slightly improved version using leading separators instead of trailing. Oh, and yes, I've fixed the warnings as well:


create function foldername (this_id integer)
returns varchar(255)
deterministic
reads sql data
begin
  declare result varchar(255) default '';
  declare this_name varchar(255) default '/';
  declare parent_id integer;
  set parent_id = this_id;

  while this_id is not null and this_id > 0
  do
    set parent_id = null;

    select name, parent
    into this_name, parent_id
    from folders where id = this_id;

    set this_id = parent_id;
    if parent_id is not null
    then
      set result = concat('/', this_name, result);
    end if;
  end while;

  return result;
end;
!!

select *,foldername(id) from folders;
+----+-----------+--------+--------------------------+
| id | name      | parent | foldername(id)           |
+----+-----------+--------+--------------------------+
|  1 | docs      |      0 | /docs                    |
|  2 | old       |      1 | /docs/old                |
|  3 | new       |      1 | /docs/new                |
|  4 | deep      |      3 | /docs/new/deep           |
|  5 | very deep |      4 | /docs/new/deep/very deep |
+----+-----------+--------+--------------------------+
5 rows in set, 0 warnings (0.01 sec)