CODEWITHSUNDEEP
phpmysqlsql
j. marc
j. marc

Reputation: 383

MySQL - Sort and group posts/comments by different criteria

I have an SQL statement which orders comments in order of time posted and also groups sub-replies to the parent comment. 

 |- Comment 1      (Oldest)
  |- Sub Comment   (Sub Oldest)
  |- Sub Comment   
  |- Sub Comment   (Sub Newest)
 |- Comment 2      (Newest)

The query is below

SELECT 
  *
FROM feed_comments c
WHERE c.feed = 50
GROUP BY c.id
ORDER BY 
       IF(c.parent_id IS NULL, c.id, parent_id), 
       c.time ASC

I want to modify the query to still group the comments in the same way however I would like the main comments to be NEWEST to OLDEST and keeping the sub replies OLDEST TO NEWEST

 |- Comment 1      (Newest)
  |- Sub Comment   (Sub Oldest)
  |- Sub Comment   
  |- Sub Comment   (Sub Newest)
 |- Comment 2      (Oldest)

I've been playing around with many different combinations of IF and CASE statements but can't seem to find the right solution!

Upvotes: 0

Views: 71

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270401

Your query doesn't reference a date/time column, so I have to guess on how you represent "newest". Let me guess that this refers to larger ids.

You can also simplify the expression using coalesce().

SELECT c.*
FROM feed_comments c
WHERE c.feed = 50
GROUP BY c.id
ORDER BY coalesce(c.parent_id, c.id) desc, 
         c.time ASC

Upvotes: 1

Related Questions