Selecting All Children From All Parents in mySQL

Question

I have a simple table like this:

+------------+---------+-----------+--------------+
| comment_id | post_id | parent_id | comment_text |
+------------+---------+-----------+--------------+
|          1 |     200 |         0 |          a   |
|          2 |     200 |         0 |          b   |
|          3 |     200 |         1 |          c   |
|          4 |     200 |         1 |          d   |
|          5 |     200 |         0 |          e   |
|          6 |     200 |         2 |          f   |
|          7 |     200 |         2 |          g   |
|          8 |     200 |         0 |          h   |
|          9 |     200 |         0 |          i   |
|         10 |     200 |         1 |          k   |
+------------+---------+-----------+--------------+

• Column parent_id tells us that this comment is a reply to another comment with this id.

• Let's say there is only one level nesting of comments.

Now I need to return only first 5 parent comments and all of the children that belong to them.

I have a query for that but there is one problem.

    (
     SELECT c.* 
     FROM comments AS c 
     WHERE c.post_id = '200' AND parent_id='0'
     LIMIT 0,5
    )
    UNION 
    (
     SELECT c.*
     FROM comments AS c
     WHERE c.post_id = '200' AND c.parent_id IN 
     (
      SELECT c.comment_id
      FROM comments AS c 
      WHERE c.post_id= '200' AND parent_id='0'
      LIMIT 0,5
     )
    )

The problem is that my current mySQL version doesn't support LIMIT in sub-queries. Also it seems that I execute same SELECT query twice!

I'm sure there must be a way to do this more elegantly.

Answer 1

This is the most elegant way to do it: http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/. The one issue you'll run into is that inserts will take some time as the other rows need to be updated; however, this can be avoided with some creativity. That article is a great start.

Answer 2

In the end I ended up using something like what Andomar suggested but without the subquery:

        (
        select  *
        from    comments c
        where   c.post_id = '200' 
                and c.parent_id = 0
        order by comment_id DESC limit 0, 4
        )
union
       (
        select  *
        from    comments c
        join    comments p
        on      c.parent_id = p.comment_id
        where   c.post_id = '200' 
                and c.parent_id != '0'
        )
        order by comment_id DESC

EDIT: As noted correctly by niaher this query returned a much larger result set than required. That makes this solution wrong.

Fortunately I have found another way to do it, much more elegant and cleaner.

Correct way to do it:

SELECT c.*
    FROM (
        SELECT comment_id
        FROM comments
        WHERE post_id = 200 AND parent_id = 0
        ORDER BY comment_id ASC
        LIMIT 0,5       
    ) AS q
JOIN comments AS c
    ON c.comment_id = q.comment_id OR c.parent_id = q.comment_id

I've tested this query, and it runs MUCH faster than the previous one.