0
193
ViewsHow to create hard SQL request in MySQL (recursion)
Sorry for my not perfect english, but I need some help.
I heard that version 5 does not support recursion in the query, but version 8 does, I also heard that you can bypass the limitation of version 5 and make a recursive query.
There is such a table with data for example
CREATE TABLE `Example` (
`id` int NOT NULL,
`parent_id` int NOT NULL DEFAULT '1',
`name` varchar(512) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8_general_ci;
INSERT INTO `Example` (`id`, `parent_id`, `name`) VALUES
(1, 0, 'Something 1'),
(2, 1, 'Something 2'),
(3, 2, 'Something 3'),
(4, 1, 'Something 4');
From this table, I have to get data in this form: This is an array of objects, inside which there are keys with table data and a datф value, which is also an array of objects
[
{
id: 1,
name: 'Something 1',
parent_id: 0,
data: [
{
id: 2,
name: 'Something 2',
parent_id: 1,
data: [
{
id: 3,
name: 'Something 3',
parent_id: 2,
data: []
},
],
},
{
id: 4,
name: 'Something 4',
parent_id: 1,
data: []
},
],
},
]
·
Juan Pablo Isaza
1 answers
Answer question0
This is not a complete solution, but I hope someone could complete it or you can do it in a programming language after here.
WITH recursive cte_name AS
(
SELECT id,
parent_id,
NAME,
1 lvl
FROM example test
UNION ALL
SELECT e.id,
e.parent_id,
e.NAME,
lvl+1
FROM cte_name c
INNER JOIN example e
ON c.id = e.parent_id ), rec_tab AS
(
SELECT id,
parent_id,
NAME,
row_number() OVER (partition BY parent_id, NAME ORDER BY lvl DESC) AS path_pattern
FROM cte_name), path_tab as (
SELECT id,
group_concat(DISTINCT parent_id ORDER BY path_pattern DESC separator ' ') path
FROM rec_tab
GROUP BY id)
select * from path_tab;
As you can see, path starts from 0(zero) for every root.
If someone complete solution for requested format, I would also appreciate it.
Thanks!
·
Juan Pablo Isaza
Report
Answer question