Nested Comment System Mysql Ordering

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 0

Solution 1:

Consider this example...

DROPTABLE IF EXISTS comments;

 CREATETABLE comments
 (comment_id INTNOTNULL AUTO_INCREMENT PRIMARY KEY
 ,comment VARCHAR(50) NOTNULL   
 ,comment_date DATETIME
 ,parent_id INTNULL
 );

 INSERTINTO 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-0410:10:10',1),
 (5     ,'Ça va?',                          '2013-03-0510:10:10',2),
 (6     ,'Je vais bien, merci, et toi?',    '2013-03-0610:10:10',2),
 (7     ,'Yes, not too bad thanks',         '2013-03-0710:10:10',1),
 (8     ,'Oui, comme ci comme ça.',         '2013-03-0810:10:10',2),
 (9     ,'Bon, à bientôt.',                 '2013-03-0910:10:10',2),
 (10    ,'See you soon',                    '2013-03-1010: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-0410:10:10|1||1| Hello   |2013-03-0110:10:10|NULL|7| Yes, not too bad thanks      |2013-03-0710:10:10|1||1| Hello   |2013-03-0110:10:10|NULL|10| See you soon                 |2013-03-1010:10:10|1||2| Bonjour |2013-03-0210:10:10|NULL|5| Ça va?                       |2013-03-0510:10:10|2||2| Bonjour |2013-03-0210:10:10|NULL|6| Je vais bien, merci, et toi? |2013-03-0610:10:10|2||2| Bonjour |2013-03-0210:10:10|NULL|8| Oui, comme ci comme ça.      |2013-03-0810:10:10|2||2| Bonjour |2013-03-0210:10:10|NULL|9| Bon, à bientôt.              |2013-03-0910:10:10|2|+------------+---------+---------------------+-----------+------------+------------------------------+---------------------+-----------+

Solution 2:

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
    WHEN0THEN CONCAT_WS('.',id,LPAD((SELECTMAX(id)+1FROM Comments WHERE orig_comment = C.id),3,'0'))
    ELSE CONCAT_WS('.',orig_comment,LPAD(id,3,'0'))
ENDAS sort
FROM Comments as C
ORDERBY 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)