In the meantime i create a simple ‘Hierarchy style’ SQL select.
/*
* Keywords in hierarchy, like 'What\Bird\Pelican\Brown Pelican' style
* Fields:
* key_hierarchy_name -> the 'full path' name
* key_hierarchy_name -> the 'full path' in pl, database ID
* Note: its limited to 4 level like: 1\2\3\4 (1 is the root, so may better if we call 3 level)
* Note: i know with recursive sql is more nice (its just force and limited), but i think 1\2\3\4 is just fine (and thats the time i have)
*/
SELECT
key_hierarchy_name
, key_hierarchy_id
FROM
(
SELECT
lvl3.*
, (CASE
when x_parent_3_value is not null then (concat(x_parent_3_value, "\", x_parent_2_value, "\", x_parent_1_value, "\", key_value))
when x_parent_3_value is null and x_parent_2_value is not null then (concat(x_parent_2_value, "\", x_parent_1_value, "\", key_value))
when x_parent_2_value is null and x_parent_1_value is not null then (concat(x_parent_1_value, "\", key_value))
else key_value
END) as key_hierarchy_name
, (CASE
when x_parent_3_value is not null then (concat(x_parent_3_id, "\", x_parent_2_id, "\", x_parent_1_id, "\", key_id))
when x_parent_3_value is null and x_parent_2_value is not null then (concat(x_parent_2_id, "\", x_parent_1_id, "\", key_id))
when x_parent_2_value is null and x_parent_1_value is not null then (concat(x_parent_1_id, "\", key_id))
else key_id
END) as key_hierarchy_id
FROM
(
SELECT
k.Id as key_id
, k.Value as key_value
, (SELECT k2.Value from Keywords k2 where k2.Id = k.ParentId) as x_parent_1_value
, (SELECT k3.Value from Keywords k3 where k3.id = ((SELECT k2.ParentId from Keywords k2 where k2.Id = k.ParentId))) as x_parent_2_value
, (SELECT k4.Value from Keywords k4 where k4.id = ((SELECT k3.parentid from Keywords k3 where k3.id = ((SELECT k2.ParentId from Keywords k2 where k2.Id = k.ParentId))))) as x_parent_3_value
, (SELECT k2.Id from Keywords k2 where k2.Id = k.ParentId) as x_parent_1_id
, (SELECT k3.Id from Keywords k3 where k3.id = ((SELECT k2.ParentId from Keywords k2 where k2.Id = k.ParentId))) as x_parent_2_id
, (SELECT k4.id from Keywords k4 where k4.id = ((SELECT k3.parentid from Keywords k3 where k3.id = ((SELECT k2.ParentId from Keywords k2 where k2.Id = k.ParentId))))) as x_parent_3_id
from Keywords k
) lvl3
) lvl3_sum
order by key_hierarchy_name
Results like:
