jkrzefski jkrzefski - 7 months ago 36
SQL Question

Tree structure reversed (MySQL)

I have a category tree that is structured in two ways: Every category has a path and a parentID. The path is made from the IDs (bottom to top) of the categories. The parentID references another category. So my table look like this:

id | name | path | parentID
---+-------------+---------+---------
1 | Root | NULL | NULL
2 | Main | NULL | 1
3 | Electronics | |2| | 2
4 | Computers | |3|2| | 3
5 | PCs | |4|3|2| | 4
6 | Macs | |4|3|2| | 4
7 | Cameras | |3|2| | 3
8 | Canon | |7|3|2| | 7


Now I don't need the categories 'Root' and 'Main'. What I try to achieve is an output like this:

id | resolved_path
---+-----------------------------
3 | Electronics
4 | Electronics_Computers
5 | Electronics_Computers_PCs
6 | Electronics_Computers_Macs
7 | Electronics_Cameras
8 | Electronics_Cameras_Canon


So I have a varying depth and I need the categories to be in reversed order. I didn't really find much about this online. All I got is this snippet which shows the depth of the category:

SELECT
*,
(ROUND(
(LENGTH(cat.path) - LENGTH(REPLACE(cat.path, '|', ''))) / LENGTH('|')
) - 2) depth
FROM
categories cat
WHERE
cat.path IS NOT NULL


I don't know what is easier: going through the parentIDs recursively or doing some magic to the paths.

Answer

You could use this (not optimized) function as a baseline:

DELIMITER //
DROP FUNCTION IF EXISTS extract_path //
CREATE FUNCTION extract_path(idlist VARCHAR(255))
RETURNS LONGTEXT
BEGIN
    DECLARE result LONGTEXT;
    DECLARE tmplist VARCHAR(255);
    DECLARE buffer VARCHAR(255);
    DECLARE lastpos INT;

    -- reverse and trim last separator (that first of reversed string)
    SELECT TRIM(BOTH FROM SUBSTRING(REVERSE(idlist), 2)) INTO tmplist;

    mainloop: LOOP
      -- split on separator
      SELECT LOCATE('|', tmplist) INTO lastpos;
      -- detect end
      IF lastpos IS NULL OR lastpos < 2
      THEN LEAVE mainloop;
      END IF;

      -- resolve next id
      SELECT cat.name INTO buffer
      FROM categories cat
      WHERE cat.id = TRIM(REVERSE(SUBSTRING(tmplist, 1, lastpos - 1)));

      -- append new element
      SELECT CONCAT(COALESCE(CONCAT(result, '_'), ''), buffer) INTO   result;

      -- prepare for next iteration
      SELECT TRIM(BOTH FROM SUBSTRING(tmplist, lastpos + 1)) INTO tmplist;

      -- detect end (corner case)
      IF tmplist IS NULL
      THEN LEAVE mainloop;
      END IF;

    END LOOP;

    RETURN result;
END //

SELECT extract_path('|1|2|3|');
-- with categories 1 -> foo ; 2 -> bar ; 3 -> baz
-- output is 'baz_bar_foo'
DELIMITER ;