Tree structure reversed (MySQL)

Question

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.

you could adapt this answer to work in reverse order : http://stackoverflow.com/a/5928675/1503505 — Preuk, Apr 22 '16 at 08:27
@Preuk My problem with that is that I have a varying depth. So I would need some kind of loop to do that with this solution. Is there any way to do that? — jkrzefski, Apr 22 '16 at 08:46
Why you don't create an intermediate table (categories_parents) that you can use for search the parent categories?. It is faster an easy. — Juan Lago, Apr 22 '16 at 09:10
@JuanLago That idea is not bad actually. Just to make sure that I understand what you mean: I would loop through the categories via php (or similar) and make a table where I store the parents to every category in? It's not really what I was looking for, since I try to do it all with MySQL but I'll give it a try. — jkrzefski, Apr 22 '16 at 09:19
I do have something similar in one application but my path is definded in revers, for example is 2|3|4 instead of 4|3|2 which allows me to order by it descending and having a simple query solution. — Elzo Valugi, Apr 22 '16 at 09:41
Take a look to this: http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/ — Juan Lago, Apr 22 '16 at 09:59

score 0 · Accepted Answer · answered Apr 22 '16 at 09:32

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 ;

@jkrzefski no problem, just ask if you need clarifications. Anyway, I suggest that you consider fixing your model as Juan Lago commented. You can extract elements from my function to build your intermediate table. — Preuk, Apr 22 '16 at 10:40

Tree structure reversed (MySQL)

1 Answers1