Mysql: Sorting newest comments to be the first and newest replies on each comment to be the last

Question

I have a table with hierarchical-data (comments with replies) and I wonder how I can sort it so that the newest comments will be prepended as the first ones and the newest replies for each comment should be appended as the last ones.

An example of the table

Here is an example of what it looks like:

id   |   path    |   reply_to   |   date
1         1            NULL         8:00
2        1-2            1           9:00
3        1-3            1          10:00
4         4            NULL        11:00
5        1-5            1          12:00
6        4-6            4          13:00
7        4-7            4          14:00

If I want to select the path of my result, it should be ordered like this:

4
4-6
4-7
1
1-2
1-3
1-5

Is this possible using MySQL queries?

What I have been doing until now

Until now, I have only selected the rows with no replies ordered by ID DESC, and then (for each row) I requested its replies and ordered them by ID ASC, I used PDO in PHP to connect to my database:

$stmt1 = $this->db->query("SELECT * FROM comments WHERE reply_to IS NULL ORDER BY id DESC");
$stmt2 = $this->db->prepare("SELECT * FROM comments WHERE reply_to = ? ORDER BY id ASC");
while($row = $stmt1->fetch()) {
    //display the comment
    $stmt2->execute(array($row['id']));
    while($row2 = $stmt2->fetch()) {
        //display all its replies
    }
}

But I think if I query only one time it would be much better than querying inside of a loop like I am doing now, right?

I hope my question is understandable. Unfortunately, I have no idea how to solve this problem. I thought that SQL Joins are the solution, but as far as I know they are "used to combine rows from two or more tables, based on a common field between them".

Here is an SQL Fiddle to play around.

Thanks in advance.

BTW, I think '/' is the convention for materialized paths, so '1-3' becomes '/1/3' — Strawberry
– Strawberry, Commented Aug 25, 2014 at 11:59

Gordon Linoff · Accepted Answer · 2014-08-25 18:50:24Z

3

This is a bit complicated, but you can do it (at least for the data shown in the question). The idea is to bring in the parent information ("reply to"). Then sort by the following:

Parent's date and parent's id -- so all parent messages are together
Then put the parent first
Then order the rest by the time

This is the query:

select c.*
from comments c left join
     comments cparent
     on c.reply_to = cparent.id
order by coalesce(cparent.date, c.date) desc,
         coalesce(cparent.id, c.id),
         (cparent.id is null) desc,
         c.date asc;

edited Aug 25, 2014 at 18:50

answered Aug 25, 2014 at 11:13

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Bluedayz Over a year ago

I get an error, I can't use the joined table cparent in the order by clause: "#1054 - Unknown column 'cparent.date' in 'order clause'"

Gordon Linoff Over a year ago

@Bluedayz . . . That error doesn't make sense. If the column is in the table, you should be able to order by it.

Bluedayz Over a year ago

I changed c.date desc to c.date asc, it worked then

Andrzej Reduta · Accepted Answer · 2014-08-25 11:26:59Z

2

easiest way:

  SELECT c.* FROM 
    comments c
  ORDER BY LEFT(c.path,1) DESC, c.path ASC

edited Aug 25, 2014 at 11:26

answered Aug 25, 2014 at 11:21

Andrzej Reduta

7771 gold badge7 silver badges15 bronze badges

2 Comments

Bluedayz Over a year ago

Works, will test the other ones too

Bluedayz Over a year ago

I have to say that this does not work with numbers greater than 10. The numbers < 9 will be displayed lastly and the replies for each comment come before the comment itself. The output will be: 11, 12, 13, 14-1, 14-2, 14, 15-1, 15, 1, 2, 3....

krtek · Accepted Answer · 2014-08-25 11:09:48Z

0

You could probably do it in one query doing something like that

SELECT comment.path
FROM comments AS base
LEFT JOIN comments AS comment ON (
    comment.id = base.id OR -- join the parents
    comment.reply_to = base.id -- join the children
)
WHERE base.reply_to IS NULL
ORDER BY comment.id

answered Aug 25, 2014 at 11:09

krtek

26.7k5 gold badges60 silver badges84 bronze badges

Collectives™ on Stack Overflow

Mysql: Sorting newest comments to be the first and newest replies on each comment to be the last

An example of the table

What I have been doing until now

3 Answers 3

3 Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

An example of the table

What I have been doing until now

3 Answers 3

3 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related