I have this scenario with this table:
CREATE TABLE `elements` (
`ID` bigint(20) UNSIGNED NOT NULL,
`ref_element_id` bigint(20) UNSIGNED NOT NULL,
`ref_element_id_parent` bigint(20) UNSIGNED DEFAULT NULL,
`name` varchar(250) COLLATE utf8mb4_unicode_ci NOT NULL
);
which contains some elements like:
ID ref_element_id ref_element_id_parent name
171 , 211388375 , 211388376 , 'element_10'
158 , 211388376 , 211388243 , 'element_9'
131 , 211388243 , 211388242 , 'element_8'
149 , 211388242 , 211388241 , 'element_7'
150 , 211388241 , 211388240 , 'element_6'
153 , 211388240 , 211388239 , 'element_5'
146 , 211388239 , 211388238 , 'element_4'
136 , 211388238 , 211388237 , 'element_3'
where parent of element_10 is element_9 and so on...
Actually I used to run a recursive query with with recursive clause on a database like :
SELECT
A.ref_element_id,
A.ref_element_id_parent,
A.name,
A.level
FROM (
with recursive parent_users (ref_element_id, ref_element_id_parent, name, level) AS (
SELECT ref_element_id, ref_element_id_parent, name, 1 level
FROM elements
WHERE ref_element_id = 211388375
union all
SELECT t.ref_element_id, t.ref_element_id_parent, t.name, level + 1
FROM elements t INNER JOIN parent_elements pu
ON t.ref_element_id = pu.ref_element_id_parent
)
SELECT * FROM parent_elements ) A LIMIT 10
that would return to me this:
ID ref_element_id ref_element_id_parent name level
171 , 211388375 , 211388376 , 'element_10', 1
158 , 211388376 , 211388243 , 'element_9', 2
131 , 211388243 , 211388242 , 'element_8', 3
149 , 211388242 , 211388241 , 'element_7', 4
150 , 211388241 , 211388240 , 'element_6', 5
153 , 211388240 , 211388239 , 'element_5', 6
146 , 211388239 , 211388238 , 'element_4', 7
136 , 211388238 , 211388237 , 'element_3', 8
but since I have moved this table on a mysql 5.7 db which doesn't support this convenient with recursive clause.... how I could modify that query to obtain the same result?
Thanks to all in advance! Cheers!