With recursive cte query in MySQL

Question

CREATE TABLE `comments` (
`comment_id` int NOT NULL,
`user_id` int DEFAULT NULL,
`comment` varchar(32) DEFAULT NULL,
`post_id` int DEFAULT NULL,
`parent_comment_id` int DEFAULT NULL,
PRIMARY KEY (`comment_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

WITH RECURSIVE cte AS 
    ( SELECT 
             comment,
             comment_id AS path,
             user_id,
             post_id
      FROM
             comments
      WHERE parent_comment_id IS NULL 
      UNION ALL
      SELECT 
         child.comment,
         CONCAT(parent.path,'/',child.name) AS comment_id,
         user_id,
         post_id
      FROM
         comments parent , comments child
      WHERE 
         child.parent_comment_id = parent.comment_id )
SELECT * FROM cte;

ERROR 1054 (42S22): Unknown column 'parent.path' in 'field list'

First time trying recursive sql query.

The query is not working for me. Can some one please help to fix the recursive query ?

Answer 1

There are several things to change in your example, so this question isn't a duplicate of some past question.

WITH RECURSIVE cte(comment, path, comment_id, user_id, post_id) AS
    ( SELECT
             comment,
             cast(comment_id as char),
             comment_id,
             user_id,
             post_id
      FROM
             comments
      WHERE parent_comment_id IS NULL
      UNION ALL
      SELECT
         child.comment,
         CONCAT(parent.path,'/',child.comment_id),
         child.comment_id,
         child.user_id,
         child.post_id
      FROM
         cte parent INNER JOIN comments child
      ON
         child.parent_comment_id = parent.comment_id 
    ) 
SELECT * FROM cte;

Notes:

• Define the column names in the first line. As with any UNION query, the column names are defined once. The first column definitions override any column names or aliases defined in the individual queries of the UNION.
• Because comment_id is an int, it must be cast to a string in the base case query, or else concatenation in the recursive case query won't work.
• The query needs the individual comment_id as well as the concatenated path, to make it easier to join to the child elements in the recursive case.
• Make a habit of using the SQL-92 JOIN syntax instead of the outdated "comma join" syntax.

Dbfiddle to demo: https://dbfiddle.uk/ttB_0PSJ