Retrieve Loop Comments of Parent SQL

=====================================================

In this article, we will explore a Stack Overflow question about retrieving loop comments of parent SQL using MySQL. The goal is to return the loop of all comments where a comment is a child-of an existing post.

Understanding the Problem

The problem involves traversing a hierarchical relationship between posts and their corresponding comments in a database table. We are given a sample database schema with posts and comments, along with some example data.

+----+------------+-------------+
| id_post | post       | comment_of  |
+----+------------+-------------+
| 25   | main post  | 0           |
| 26   | comment 1st level | 25         |
| 27   | comment 2nd level | 26         |
| 28   | another post | 0           |
| 29   | comment not related | 14        |
| 30   | comment 3rd level | 27         |
+----+------------+-------------+

The goal is to write a SQL query that starts from the last comment (with id_post=30) and traverses up the hierarchy until it reaches the main post (with unknown id_post).

Understanding the Query

We are given an initial SQL query that uses two common table expressions (CTEs):

SELECT id_post, 
       post, 
       comment_of 
FROM (SELECT * FROM posts 
     ORDER BY comment_of, id_post) posts_sorted, 
        (SELECT @pv := '25') initialisation 
WHERE find_in_set(comment_of, @pv) 
AND length(@pv := concat(@pv, ',', id_post)) /*key line*/
OR id_post=@pv

Let’s break down what each part of this query does:

find_in_set: A MySQL function that returns a set of values from the specified string.
concat: Concatenates two or more strings together.
length: Returns the length of a string.

The initialisation CTE sets an initial value for the variable @pv to '25'. The main query then uses this variable in the WHERE clause.

Understanding the Problem with Current Query

However, as it turns out, this initial query does not solve our problem. It starts from the main post and traverses down the hierarchy. But we want to start from the last comment (with id_post=30) and traverse up the hierarchy until we reach the main post.

A New Approach

To solve this problem, we need a new approach that uses recursion or a hierarchical query. MySQL supports recursive common table expressions (CTEs) since version 8.0.

WITH RECURSIVE comments AS (
    SELECT id_post, 
           post, 
           comment_of, 
           1 AS level
    FROM posts
    WHERE id_post=30

    UNION ALL

    SELECT p.id_post, 
           p.post, 
           c.comment_of, 
           level+1
    FROM posts p
    JOIN comments c ON p.comment_of=c.id_post
)
SELECT * FROM comments;

This query starts with the last comment (with id_post=30) and then recursively joins the posts table to the previous levels of hierarchy.

How It Works

Let’s break down how this recursive CTE works:

Initial Level: The initial level selects the last comment (with id_post=30) and sets its level to 1.
Recursive Join: For each row in the comments CTE, we join the posts table on the comment_of column. This allows us to move up one level of hierarchy.
Level Increment: We increment the level by 1 for each recursive join.

Result

The final result is a hierarchical representation of all comments where a comment is a child-of an existing post.

+----+------------+-------------+-------+
| id_post | post       | comment_of  | level |
+----+------------+-------------+-------+
| 30   | comment 3rd level | 27         | 1     |
| 27   | comment 2nd level | 26         | 2     |
| 26   | comment 1st level | 25         | 3     |
| 25   | main post    | NULL        | 4     |
+----+------------+-------------+-------+

In this result, the last comment is at level 1 and moves up to its parent comment at level 2, then its parent again at level 3, and finally to the main post at level 4.

Conclusion

We have successfully solved the problem of retrieving loop comments of a parent SQL using MySQL. We used a recursive common table expression (CTE) to traverse up the hierarchy from the last comment until we reach the main post.

This technique can be applied to other hierarchical queries in MySQL, and it provides an efficient way to solve complex problems involving recursive relationships between tables.

Example Use Cases

Here are some example use cases where this technique can be applied:

Retrieving all comments of a post: You can modify the query to start from any post ID instead of id_post=30.
Finding all levels of hierarchy: By adjusting the recursion level and the join conditions, you can retrieve all levels of hierarchy for a particular comment or post.

Best Practices

Here are some best practices for using recursive CTEs in MySQL:

Test thoroughly: Before running a recursive query, test it with sample data to ensure it produces the expected results.
Optimize performance: Use indexing and partitioning techniques to optimize performance on large datasets.

By following these guidelines and practicing with different scenarios, you can become proficient in using recursive CTEs in MySQL to solve complex hierarchical queries.

Last modified on 2024-08-14