EXPLAIN EXTENDED

Today, we will improve the query a little more.

If we develop the catalog with categories, subcategories etc, it's useful to have breadcrumb links somewhere in the header of the page.

In Oracle, there is a special function called SYS_CONNECT_BY_PATH(row_expression, delimiter), that returns the ancestry chain of a given node.

For any given row in a hierarchical query, it will return the delimiter separated list of row_expressions calculated for each of the ancestor rows, parents first.

We can emulate this function in MySQL too:

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

This function will work even without initializing session variables. In this case it will return the full ancestry chain path up to the first orphan node.

If @start_with is initialized, the function will return the ancestry chain up to @start_with row.

Let's see how it works:

SELECT  CONCAT(REPEAT('    ', level - 1), hi.id) AS treeitem,
        hierarchy_sys_connect_by_path('/', hi.id) AS path,
        parent, level
FROM    (
        SELECT  hierarchy_connect_by_parent_eq_prior_id_with_level(id, 2) AS 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

The function result differs a little from what Oracle returns, because the latter appends the delimiter to the beginning of the expression returned, while our function uses delimiter only as a separator.

I made it this way because it's almost always undesirable to have the delimiter in the beginning of the result. And it's much easier to append the delimiter when we need it than to get rid of when we don't.

To be continued.