SQL Flatten inheritance

Question

I have this groups table in MySQL

id	name	parent_id
1	A	null
2	B	null
3	C	null
4	A-A	1
5	A-B	1
6	A-A-A	4
7	A-B-A	5
8	A-B-A-A	7
9	B-A	2

I want to create a MySQL view with every pair of a child and any of its parents. Like this

child_id	parent_id
4	1
5	1
6	4
6	1
7	5
7	1
8	7
8	5
8	1
9	2

I have tried using recursive like the following but this only creates pairs for the first level of parents.

CREATE VIEW groups_inheritance AS
WITH RECURSIVE cte AS (SELECT id AS child_id, parent_id
                       FROM groups
                       UNION ALL
                       SELECT g.id AS child_id, g.parent_id
                       FROM groups g
                                INNER JOIN cte ON g.id = cte.parent_id)
SELECT *
FROM cte

I also found this in another thread and as far as I understand it creates pairs for up to the second level.

WITH RECURSIVE generation AS (
    SELECT id,
         parent_id,
         0 AS generation_number
    FROM groups
    WHERE parent_id IS NULL

UNION ALL

    SELECT g.id,
         g.parent_id,
         generation_number+1 AS generation_number
    FROM groups g
    inner JOIN generation gen
      ON gen.id = g.parent_id

)
SELECT *
FROM generation gen
 JOIN groups parent
 ON gen.parent_id = parent.id;

The application does not limit the user to the level of nesting, so I am looking for a solution covering any level of nesting. Otherwise I If that is a problem I think the max level it could be reached is 6. Do you have any suggestions on how to create that MySQL view?

Answer 1

WITH RECURSIVE
cte AS (
    SELECT id, parent_id, CAST(id AS CHAR(65535)) path
    FROM `groups`
    WHERE parent_id IS NULL
    UNION ALL
    SELECT `groups`.id, cte.id, CONCAT_WS(',', cte.path, `groups`.id)
    FROM cte
    JOIN `groups` ON cte.id = `groups`.parent_id
)
SELECT t1.id child_id, t2.id parent_id
FROM cte t1
JOIN cte t2 ON LOCATE(t2.path, t1.path) AND t1.id <> t2.id;

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=fb8a506b120c5ff0fe028c92cf7f5b0e

Of course, this query does not provide "a solution covering any level of nesting" which is defined by the maximal length defined for path column of CTE (from the other side some reasonable limit must exist - define and use it).