Nested comment system mysql ordering

Question

Ok, I realize there are many ways to accomplish comments. The way I have chosen is a single table set up like this.

id    comment    date       time    orig_comment
1     Hello   03-01-2013  10:10:10       0
2     Hello   03-02-2013  10:10:10       0
3     Hello   03-03-2013  10:10:10       1

So to clearify there is a first level and then users can reply to that comment(these are the only two levels). I give each unique incrementing id, and specify orig_comment. If orig_comment is "0" then it is a base level comment, if it is nested then orig_comment will be the id of the original comment. Simple enough. So I need the comments to be ordered of course. Here is how I do this as of now.

mysql_query("SELECT Comments.* FROM Comments ORDER BY 
IF(Comments.orig_comment = 0, Comments.id, Comments.orig_comment)DESC,Comments.id ASC")

This works to get the newest comments retrieved first(which is what I want), but when it comes to nested comments it orders them by oldest first(not what I need). I need to order primary comments from newest to oldest, and any replies should be ordered also from newest to oldest. I have tried without success to modify my query to do this but cannot figure it out.

As a side question does this system make sense from a usabality standpoint? Do you think ordering nested comments that way would be confusing? I wanted to do it this way because my reply button is located on the base comment, and once clicked adds a textarea right below the base comment. I just thought it would be easier to see your post if it too came right below the base comment instead of being thrown all the way to the bottom of the nested replies. Any thoughts on this?

Also want to address another question I have using this to page comments. I can use a simple limit to get only the x number of comments like this:

mysql_query("SELECT Comments.* FROM Comments ORDER BY 
IF(Comments.orig_comment = 0, Comments.id, Comments.orig_comment)DESC,Comments.id ASC 
LIMIT 0, $page")

This method obviously does not pay attention to the number of replies to each main comment. So I end up cutting off replies to the very last main comment. I would like to enforce the limit only when orig_comment='0', so that no matter how many replies there are to a comment it will show all of them. I tried

mysql_query("SELECT Comments.* FROM Comments ORDER BY 
IF(Comments.orig_comment = 0, Comments.id, Comments.orig_comment)DESC,Comments.id ASC 
LIMIT 0, SELECT COUNT(id)FROM Comments WHERE orig_comment='0' LIMIT $page")

This throws a syntax error though.

Shouldn't it be Comments.id DESC if you want to sort with newest first? Doesn't the IF say sort descending by id for base level, and descending by origin_comment for others. You may use case statement instead: http://stackoverflow.com/questions/3550942/can-you-add-if-statement-in-php-mysql-order-by — apelsinapa, Mar 21 '13 at 02:35
Doing so ends up putting the newest reply at the top without any base comment before it. I also have tried to integrate the query from that very article but again I end up with a reply on the top without a base comment before it. — dminicrick1, Mar 21 '13 at 02:50

score 1 · Answer 1 · answered Mar 21 '13 at 16:10

Consider this example...

 DROP TABLE IF EXISTS comments;

 CREATE TABLE comments
 (comment_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
 ,comment VARCHAR(50) NOT NULL   
 ,comment_date DATETIME
 ,parent_id INT NULL
 );

 INSERT INTO comments VALUES
 (1     ,'Hello',                           '2013-03-01 10:10:10',NULL),
 (2     ,'Bonjour',                         '2013-03-02 10:10:10',NULL),
 (3     ,'How are you?',                    '2013-03-03  10:10:10',1),
 (4     ,'I\'m fine thank you, and you?',   '2013-03-04 10:10:10',1),
 (5     ,'Ça va?',                          '2013-03-05 10:10:10',2),
 (6     ,'Je vais bien, merci, et toi?',    '2013-03-06 10:10:10',2),
 (7     ,'Yes, not too bad thanks',         '2013-03-07 10:10:10',1),
 (8     ,'Oui, comme ci comme ça.',         '2013-03-08 10:10:10',2),
 (9     ,'Bon, à bientôt.',                 '2013-03-09 10:10:10',2),
 (10    ,'See you soon',                    '2013-03-10 10:10:10',1);

 SELECT * 
   FROM comments 
      x 
   JOIN comments y 
     ON y.parent_id = x.comment_id 
  ORDER 
     BY x.comment_date
      , y.comment_date;
 +------------+---------+---------------------+-----------+------------+------------------------------+---------------------+-----------+
 | comment_id | comment | comment_date        | parent_id | comment_id | comment                      | comment_date        | parent_id |
 +------------+---------+---------------------+-----------+------------+------------------------------+---------------------+-----------+
 |          1 | Hello   | 2013-03-01 10:10:10 |      NULL |          3 | How are you?                 | 2013-03-03 10:10:10 |         1 |
 |          1 | Hello   | 2013-03-01 10:10:10 |      NULL |          4 | I'm fine thank you, and you? | 2013-03-04 10:10:10 |         1 |
 |          1 | Hello   | 2013-03-01 10:10:10 |      NULL |          7 | Yes, not too bad thanks      | 2013-03-07 10:10:10 |         1 |
 |          1 | Hello   | 2013-03-01 10:10:10 |      NULL |         10 | See you soon                 | 2013-03-10 10:10:10 |         1 |
 |          2 | Bonjour | 2013-03-02 10:10:10 |      NULL |          5 | Ça va?                       | 2013-03-05 10:10:10 |         2 |
 |          2 | Bonjour | 2013-03-02 10:10:10 |      NULL |          6 | Je vais bien, merci, et toi? | 2013-03-06 10:10:10 |         2 |
 |          2 | Bonjour | 2013-03-02 10:10:10 |      NULL |          8 | Oui, comme ci comme ça.      | 2013-03-08 10:10:10 |         2 |
 |          2 | Bonjour | 2013-03-02 10:10:10 |      NULL |          9 | Bon, à bientôt.              | 2013-03-09 10:10:10 |         2 |
 +------------+---------+---------------------+-----------+------------+------------------------------+---------------------+-----------+

i wouldn't be able to order by date because that would be too broad; on any given day there could be hundreds of comments posted. i'd rather keep it ordered by id(which it looks to be an easy mod to your query), so no matter when it was posted it will still be ordered correctly. And how would I go about paging these results(last segment of my original post)? — dminicrick1, Mar 21 '13 at 16:56
As per my example, date and time should be a single column, but if id works for you then that's fine too. — Strawberry, Mar 21 '13 at 17:03
With regards pagination, my own view is that you should return the whole result in a single query and then paginate in the application layer without hitting the database each time. — Strawberry, Mar 21 '13 at 17:10
Lot's of people disagree with this approach, but my argument runs like this: Say you return results in reverse chronological order, with one result per page. It takes 5 seconds to read a page, in which time someone else has posted a response. Then, when you page forward, you end up just seeing your previous page again! But I'm not an expert on this. — Strawberry, Mar 21 '13 at 17:12
I have thought about doing it that way, and just allowing my php loop to count the main comments as well as replies. Any particular reason you prefer that method? It would just seem somewhat more difficult, since all after the page limit would I guess have to be hidden on the page and have javascript unhide them as you want to see more? — dminicrick1, Mar 21 '13 at 17:13
I have used sites where that exact example does occur and it is annoying. If I did though want to have mysql do the work though how would I modify the query as such? Say I wanted to limit the main comments retrieved to 10, while at the same time retrieve all of the replies with no limit? — dminicrick1, Mar 21 '13 at 17:17

apelsinapa · Answer 2 · 2013-03-21T17:32:57.650

0

Thanks to the answer in your comment and I tried out and found a solution, it's not very beautiful but it seems to get the job done.

SELECT *, CASE orig_comment
    WHEN 0 THEN CONCAT_WS('.',id,LPAD((SELECT MAX(id)+1 FROM Comments WHERE orig_comment = C.id),3,'0'))
    ELSE CONCAT_WS('.',orig_comment,LPAD(id,3,'0'))
END AS sort
FROM Comments as C
ORDER BY sort DESC

output will be:

id  comment time        orig_comment    sort
2   Hello   "2013-03-21 16:19:00"   0   2.005
3   Hello   "2013-03-21 16:19:00"   2   2.003
4   Hello   "2013-03-21 16:19:00"   2   2.004
1   Hello   "2013-03-21 16:19:00"   0   1

This will not be troublesome when sorting, and allows up to 999 sub-comments (due to LPAD value 3) What it does is creates a sorting string, and then casts it to a decimal to get the sorting right. (however i think mysql does handle it right anyways.)

Even though this works I recommend computing the sorting value pre-hand hand, then create a good value for base level comments. (maybe set it to 2.9 or whatever will suit your needs)

edited Mar 21 '13 at 17:32

answered Mar 21 '13 at 16:05

apelsinapa

575
3
9

I am totally unfamiliar with using LPAD. So to try to see what the query does...it basically adds .reply_id to the base comment if it is a reply, and .orig_id to the base comment if it is a base comment. Then it uses the decimal value to order them from base_comment.base_comment to base_comment.last_reply? Maybe I'm missing something, is that correct? And if so, how would I compute the decimal value beforehand? Add a column to my table and calculate each time a reply is made? Would I then have to recalculate when one is deleted? – dminicrick1 Mar 21 '13 at 17:05
@dminicrick1 LPAD just fills out a string with n characters. You can se what it does. I can not see that sorting will be affected if you delete comments. (I added output so you can follow what happens). comment (id 2) will have a sort 2.005 because it has two sub-comments, and the highest id of those is 4. To get the base comment to sort before the other comments you have to create a "higher number" this can be done in different ways as i explained. To calculate: when a reply is made just create the sorting decimal value. Though this solution only support 1 level sub-comments. – apelsinapa Mar 21 '13 at 17:38

Nested comment system mysql ordering

2 Answers2

Linked