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

Can you just do 2 selects?

Select * from comments where parentID = 0 limit 5

select * from comments where parentID IN (*list of the 5 parentIDs)

Answer 2

On my version of MySQL, you can use limit in subqueries:

select  *
from    (
        select  *
        from    comments c
        where   c.post_id = '200' 
                and c.parent_id = 0
        limit    0, 3
        ) s
union
select  c.*
from    (
        select  *
        from    comments c
        where   c.post_id = '200' 
                and c.parent_id = 0
        limit    0, 3
        ) p
join    comments c
on      c.parent_id = p.comment_id;

The duplicate select is hard to avoid without the with statement, which MySQL does not support. You could create a temporary table to store them:

drop table if exists top5comments;

create table top5comments
    select  *
    from    comments c
    where   c.post_id = '200' 
            and c.parent_id = 0
    limit    0, 3;

insert  top5comments
select  children.*
from    top5comments parents
join    comments children
on      children.parent_id = parents.comment_id;

select * from top5comments;

This would require modifications if the query can be run by multiple connections at the same time.

P.S. By the way, a limit statement is usually accompanied by an order by clause. Without the order by, you'll get arbitrary rows, and the results can differ from query to query.

Answer 3

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

Answer 4

This is the most elegant way to do it: http://dev.mysql.com/tech-resources/articles/hierarchical-data.html. 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 5

Maybe you mean this:

SELECT
  *,
  (CASE WHEN parent_id = 0 THEN comment_id ELSE parent_id END) AS use_id
FROM
  comments
WHERE
  (CASE WHEN parent_id = 0 THEN comment_id ELSE parent_id END) IN (
    SELECT    comment_id
    FROM      comments
    WHERE     parent_id = 0
    ORDER BY  comment_id ASC
    LIMIT     3)
ORDER BY
  use_id ASC,
  parent_id;

What it does is take the three 'first' (ordered by comment_id ASC) parent comments and all their children and then sort the whole list by parent-/comment_id (parent first, children after).

The CASE WHEN THEN ELSE END is for SQLite (that's how I test such thingies).

-- edit

If these records exist (4 more than your 10):

comment_id  post_id  parent_id  comment
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  j
11          200      1  k
12          200      2  l
13          200      0  m
14          200      1  n

this would be the result:

comment_id  post_id parent_id   comment use_id
1        200    0   a   1
3        200    1   c   1
4        200    1   d   1
10       200    1   j   1
11       200    1   k   1
14       200    1   n   1
2        200    0   b   2
6        200    2   f   2
7        200    2   g   2
12       200    2   l   2
5        200    0   e   5