3

I have a Comments table like below, in MySQL:

content           created_at                 id  parent_id
"second comment", 2014-06-03T10:08:44+0000,  37,      -1
"third comment",  2014-06-03T10:10:35+0000,  40,      -1
"Under third",    2014-06-03T10:10:44+0000,  41,      40
"Under second",   2014-06-03T10:11:59+0000,  42,      37

Users can add new comments, which won't have a parent_id, as they are not children of other comments; users can also reply to comments that have been added through the previous method, so they are children of the primary comments, like on a second level of hierarchy. The parent_id column represents the id of a parent comment, if present. If the comment doesn't have a parent, default parent_id is -1.

That being said, I would like to query all the comments from the table, each parent followed by its children, ordered by created_at ASC. Example from the above data set:

second comment
Under second
third comment
Under third

I thought of using GROUP BY, as it resembles a grouping strategy, but not actually grouping all children into a single row. What would be a solution to this kind of query ? Are there more types of solutions ?

Bogdan Zurac
  • 6,348
  • 11
  • 48
  • 96

1 Answers1

3

It didn't test but i think this should work in MySQL too:

ORDER BY CASE WHEN parent_id=-1 THEN id ELSE parent_id END, created_at

Edit: If you cannot assume the Ids to be ascending in the same logical order as the Comments, it get's a little more complex:

SELECT parent_id,id,created_at parent_date,null child_date,content
    FROM Comments
    WHERE parent_id=-1
UNION
SELECT c.parent_id,c.id,p.created_at as parent_date,c.created_at as child_date,c.content
FROM Comments c
    JOIN (SELECT id,created_at,content
            FROM Comments
            WHERE parent_id=-1
            GROUP BY id,created_at,content) p ON p.id=c.parent_id
ORDER BY parent_date,child_date
Martin K.
  • 1,050
  • 8
  • 19
  • Indeed, this works exactly as needed. Thanks ! One issue however is that this solution depends on the premises that the comment ids are ascending as the created_at field is, right ? So if I add a comment with a lower id than any another comment, but with a higher created_at, this won't work any longer. Ex: add "fourth comment" with created_at 2015-06-03T10:11:59+0000, but with id 2, then it will show this new comment above all others. To circumvent this, do we need to add a different select query for each parent ? – Bogdan Zurac Jun 04 '14 at 12:04