Recursive self joins

Question

I have a comments table, which can be simplified down to this:

comments
=======
id
user_id
text
parent_id

where parent_id is nullable, but might be a key for its parent comment.

---

Now, how can I select all descendants of a specific comment?
The comments might be several levels down...

Answer 1

Hierarchical queries, as those recursive queries are known, are now supported in MySQL 8.

Old Answer

Alternatively, you can find a dynamic (and thus, potentially dangerous) trick here: https://stackoverflow.com/questions/8104187/mysql-hierarchical-queries

You can also find a discussion on how to store hierarchical data with other models than with an Adjacency List (i.e. the Parent column) here: https://stackoverflow.com/questions/192220/what-is-the-most-efficient-elegant-way-to-parse-a-flat-table-into-a-tree/

Good luck!

Answer 2

This table design is a SQL antipattern "Naive trees" as described by Bill Karwin (staring from slide 48 in his SQL Antipatterns Strike Back presentation). The problem with this design specifically is the difficulty with getting all descendants (or parents) of a node. Since you are using MySQL you can't use common table expressions (the WITH statement and it's RECURSIVE modifier) present in other RDBMSes.

What you're left with is:

• use an alternate implementation of hierarchical data structure (answers to this question might be a good reference on this)

• build self join queries with a depth limit. For depth = 5 you could use something in the lines of:

  SELECT *
  FROM comments AS c1
    JOIN comments AS c2 ON (c2.parent_id = c1.id)
    JOIN comments AS c3 ON (c3.parent_id = c2.id)
    JOIN comments AS c4 ON (c4.parent_id = c3.id)
    JOIN comments AS c5 ON (c5.parent_id = c4.id)
  

• use a RDBMS which supports WITH RECURSIVE (although this most likely is not an option for most people)

Answer 3

MySQL does not support recursive queries such as the one you need.

What I did a while back was write Stored Procedures that provide the model for doing so.

Rather than reinvent the wheel, I will give you the links to my past posts on this:

• Oct 24, 2011 : Find highest level of a hierarchical field: with vs without CTEs
• Dec 10, 2012 : MySQL: Tree-Hierarchical query
• Apr 12, 2013 : Recursive query in mysql

In short, the Stored Procedures I made do preorder tree traversal using queue processing

• GetParentIDByID
• GetAncestry
• GetFamilyTree

Parent to All Children (like GetFamilyTree Stored Procedure)

• STEP01) Start with a parent_id in a queue
• STEP02) Dequeue the next parent_id as the current
• STEP03) Enqueue all the id values that have the current parent_id
• STEP04) Print or Collect the Comment
• STEP05) If the queue is not empty, goto STEP02
• STEP06) You are done !!!

Child to all Parent (like GetAncestry Stored Procedure)

• STEP01) Start with an id in a queue
• STEP02) Dequeue the next id as the current
• STEP03) Enqueue the parent_id value of the current id
• STEP04) Print or Collect the Comment
• STEP05) If the queue is not empty, goto STEP02
• STEP06) You are done !!!

Please look over the Stored Procedures in my other posts to see the implementation.

Give it a Try !!!

Answer 4

SELECT  group_concat(@id :=
        (
        SELECT  id
        FROM    comments
        WHERE   parent_id = @id
        )) AS comment
FROM    (
        SELECT  @id := 1
        ) vars
STRAIGHT_JOIN
        comments
WHERE   @id IS NOT NULL

fiddle