Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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...

share|improve this question
add comment

4 Answers 4

up vote 5 down vote accepted

Hierarchical queries, as those recursive queries are known, are not supported for MySQL.

They are however supported in Oracle, Microsoft SQL Server, DB2, and PostgreSQL, amongst others.

If you need a workaround, you can find a dynamic (and thus, potentially dangerous) trick here: http://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: http://stackoverflow.com/questions/192220/what-is-the-most-efficient-elegant-way-to-parse-a-flat-table-into-a-tree/

Good luck!

share|improve this answer
    
I wonder how that solution in your second link can be dangerous. Could you explain it? Otherwise, welcome to the site! –  dezso Jul 11 '13 at 13:12
3  
@dezso: Quoth the crafter of the query himself, Quassnoi "*It's not upgrade-safe* because MySQL does not clearly define session variable behaviour. However, it's the only way to deal with adjacency lists in a timely fashion in-query." Dangerous might have been too strong a word (potentially unstable?) but I prefer to err on the side of caution (plus, I'm more knowledgeable in Oracle than MySQL, so I wanted to be extra precautious). Thanks for the welcome, by the way! I have been a long-time lurker to the SE network, and I decided it was time to pay back a bit. –  Valmoer Jul 11 '13 at 13:35
add comment

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)

share|improve this answer
2  
I disagree with Bill Karwin here. The adjacency model is not an anti-pattern. With a modern DBMS that supports recursive queries (Oracle has supported this for over 20 years) such a model is very efficient to retrieve and update. –  a_horse_with_no_name Jul 11 '13 at 14:20
add comment

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:

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 !!!

share|improve this answer
add comment
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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.