Possible recursive solution in MySQL to find comments and replies

Question

Not really (possibly) a recursive solution, but I've encountered this problem many times. I have a table called post_comments that looks like this:

Table: post_comments
comment_id    comment_text    user_id    parent_id    type
1                'x'             1           15         1
2                'hi'            2            1         2
3                'yo'            5           15         1
4                'hey'           3           1          2

Basically, type is TINYINT(1) column. If type is 2, then it means its a reply to a comment. If type is 1, it's a comment to a post. Like this:

Post: 'texttexttextloremipsmu' by some uuser
Comments:
     'x' <- Type 1
         'yo' <- Type 2, reply to 'x'

If type is 2, it means the parent_id references some comment_id in the table post_comments. If it's 1, it references a post in the posts table posts (not shown). I need an SQL query that can find all the comments and replies for a post (i.e.) for post_id = 15. It needs to return something like UNION GROUP BY where the pseudo-code is:

SELECT comment_id, type FROM post_comments WHERE parent_id = 15 and type = 1
UNION GROUP BY comment_id
SELECT comment_id FROM post_comments WHERE parent_id = comment_id
ORDER BY likes or date_posted (some arbitrary field)

To get (basically first the row is the comment and below it are the replies to the comment and this continues until all replies are listed and there are no other comments)

comment_id   type
1             1
2             2
4             2
3             1

How can I accomplish this in one query? Or is there something wrong with my database structure that is causing this problem? As well the maximum nesting possible is 1 (as in there are no replies to replies, only replies to comments)

This might help: http://stackoverflow.com/questions/23133000/mysql-find-all-parents-recursively — zedfoxus, Jun 15 '15 at 05:55

score 1 · Accepted Answer · answered Jun 15 '15 at 06:34

This would work:

SELECT * FROM 
(SELECT comment_id as new_id,comment_text from `post_comments` WHERE `type` = 1) as parent
UNION ALL
SELECT * FROM 
(SELECT parent_id as new_id,comment_text from `post_comments` WHERE `type` = 2) as child
ORDER BY `new_id`

What I basically did was consider each type as a separate table and join them based on a common id, I had to create a new column (new_id) to be able to use that for sorting, however you have an issue standing which would be what comment came first, thus I would recommend that you'd add in a created_on_date column so you'd use that as a second index to sort with.

P.S. took me almost an hour to get that for you :D

Possible recursive solution in MySQL to find comments and replies

1 Answers1