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
2. Fill Data in table
3. create function
4. Hierarchical Query
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/
For any queries please comment :-)
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;
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;
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 ;
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
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 :
For any queries please comment :-)
