sys_connect_by_path in mysql

In this we are going to introduced with mysql hierarchical data. In this post i am going to concat the hierarchical data from a table or we can say sys_connect_by_path in mysql or wm_concat in mysql.


1. create table

CREATE TABLE t_hierarchy (
        id int(10) unsigned NOT NULL AUTO_INCREMENT,
        parent int(10) unsigned NOT NULL,
        PRIMARY KEY (id),
        KEY ix_hierarchy_parent (parent, id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

2. Fill Data in table

DELIMITER $$
CREATE PROCEDURE prc_fill_hierarchy (level INT, fill INT)
BEGIN
        DECLARE _level INT;
        DECLARE _fill INT;
        INSERT
        INTO    t_hierarchy (id, parent)
        VALUES  (1, 0);
        SET _fill = 0;
        WHILE _fill < fill DO
                INSERT
                INTO    t_hierarchy (parent)
                VALUES  (1);
                SET _fill = _fill + 1;
        END WHILE;
        SET _fill = 1;
        SET _level = 0;
        WHILE _level < level DO
                INSERT
                INTO    t_hierarchy (parent)
                SELECT  hn.id
                FROM    t_hierarchy ho, t_hierarchy hn
                WHERE   ho.parent = 1
                        AND hn.id > _fill;
                SET _level = _level + 1;
                SET _fill = _fill + POWER(fill, _level);
        END WHILE;
END
$$
DELIMITER ;

START TRANSACTION;
CALL prc_fill_hierarchy(6, 5);
COMMIT;

3. create function

delimiter $$
CREATE FUNCTION hierarchy_sys_connect_by_path(delimiter TEXT, node INT) RETURNS TEXT
NOT DETERMINISTIC
READS SQL DATA
BEGIN
     DECLARE _path TEXT;
 DECLARE _cpath TEXT;
        DECLARE _id INT;
    DECLARE EXIT HANDLER FOR NOT FOUND RETURN _path;
    SET _id = COALESCE(node, @id);
      SET _path = _id;
    LOOP
                SELECT  parent
              INTO    _id
         FROM    t_hierarchy
         WHERE   id = _id
                    AND COALESCE(id <> @start_with, TRUE);
              SET _path = CONCAT(_id, delimiter, _path);
  END LOOP;
END
$$

delimiter ;

4. Hierarchical Query

SELECT  hi.id AS id,
        hierarchy_sys_connect_by_path('/', hi.id) AS tree_position,
        parent
FROM    (
        SELECT  id,
                CAST(@level AS SIGNED) AS level
        FROM    (
                SELECT  @start_with := 1,
                        @id := @start_with,
                        @level := 0
                ) vars, t_hierarchy
        WHERE   @id IS NOT NULL
        ) ho
JOIN    t_hierarchy hi
ON      hi.id = ho.id limit 10

The result will be below format :
43293    | 1/3/12/67/312/1957/9172/43293

moreover we can replace the input variable i.e. connector here we are using '/'.

Source : http://explainextended.com/2009/03/19/hierarchical-queries-in-mysql-adding-ancestry-chains/

Related posts

Procedure Hierarchical /recursive data (parent-child relationship)

For any queries please comment :-)