Comments query

Databases
1

hi guys ,
i have tables for comments and replies ,i want the comments to be sorted based on the comments and replies date
comments_tbl
[table=“width: 500”]
[tr]
[td]id[/td]
[td]uid[/td]
[td]text[/td]
[td]date[/td]

[/tr]
[/table]

replies_tbl
[table=“width: 500”]
[tr]
[td]id[/td]
[td]comment_id[/td]
[td]comment_parent_id[/td]
[/tr]
[/table]

how please ?

2

could you please explain how those tables work?

because usually, a reply is made to only one comment, so i don’t understand why you need 2 tables

why couldn’t comment_parent_id be inside the comments table?

3

its should be in one table , but this is the case now i cant change it really now

[table=“width: 500, class: outer_border, align: center”]
[tr]
[td]id[/td]
[td]uid[/td]
[td]text[/td]
[td]date[/td]
[/tr]
[tr]
[td]1[/td]
[td]003[/td]
[td]text1[/td]
[td]2011-1-26 17:58:13[/td]
[/tr]
[tr]
[td]2[/td]
[td]004[/td]
[td]text2[/td]
[td]2011-2-26 17:58:13[/td]
[/tr]
[tr]
[td]3[/td]
[td]003[/td]
[td]text1[/td]
[td]2011-3-26 17:58:13[/td]
[/tr]
[tr]
[td]4[/td]
[td]006[/td]
[td]text1[/td]
[td]2011-4-26 17:58:13[/td]
[/tr]
[tr]
[td]5[/td]
[td]006[/td]
[td]text1[/td]
[td]2011-5-26 17:58:13[/td]
[/tr]
[tr]
[td]6[/td]
[td]006[/td]
[td]text6[/td]
[td]2011-6-26 17:58:13[/td]
[/tr]
[tr]
[td]8[/td]
[td]011[/td]
[td]text11[/td]
[td]2011-7-26 17:58:13[/td]
[/tr]
[tr]
[td]9[/td]
[td]009[/td]
[td]text9[/td]
[td]2011-8-26 17:58:13[/td]
[/tr]
[tr]
[td]10[/td]
[td]017[/td]
[td]text17[/td]
[td]2011-9-20 17:58:13[/td]
[/tr]

[tr]
[td]11[/td]
[td]011[/td]
[td]text11[/td]
[td]2011-10-21 17:58:13[/td]
[/tr]

[/table]

[table=“width: 500,class: outer_border, align: center”]
[tr]
[td]id[/td]
[td]comment_id[/td]
[td]comment_parent_id[/td]
[/tr]
[tr]
[td]1[/td]
[td]2[/td]
[td]1[/td]
[/tr]
[tr]
[td]2[/td]
[td]3[/td]
[td]1[/td]
[/tr]
[tr]
[td]3[/td]
[td]5[/td]
[td]4[/td]
[/tr]
[tr]
[td]4[/td]
[td]6[/td]
[td]4[/td]
[/tr]

[tr]
[td]5[/td]
[td]11[/td]
[td]4[/td]
[/tr]
[/table]

how to sort main comments order by thier replies date if they have replies and the comments date it self

the result should be

comment_id
4 -> has new reply
10
9
8
7
1
thank you

4

oh, okay then :smiley:

SELECT c.id 
     , c.uid 
     , c.text 
     , COALESCE(r.last, c.date) as sortdate
  FROM comments AS c
LEFT OUTER
  JOIN ( SELECT replies.comment_parent_id
              , MAX(comments.date) AS last
           FROM replies
         INNER
           JOIN comments
             ON comments.id = replies.comment_id
         GROUP
             BY replies.comment_parent_id ) AS r
    ON r.comment_parent_id = c.id
ORDER
    BY sortdate DESC
5

not working fine , its displaying replies too

6

the replies should not be in the query result , because i will not display them , they will be as part of the comments

7

no, that’s not quite right – there is no comment 7 !!

SELECT c.id 
     , c.uid 
     , c.text 
     , COALESCE(r.last, c.date) as sortdate
  FROM comments_tbl AS c
LEFT OUTER
  JOIN ( SELECT replies_tbl.comment_parent_id
              , MAX(comments_tbl.date) AS last
           FROM replies_tbl
         INNER
           JOIN comments_tbl
             ON comments_tbl.id = replies_tbl.comment_id
         GROUP
             BY replies_tbl.comment_parent_id ) AS r
    ON r.comment_parent_id = c.id
 WHERE c.id NOT IN
       ( SELECT comment_id FROM replies_tbl )    
ORDER
    BY sortdate DESC
8

So you would like to list all the root level comments. However, when a root level comment has replies display a link to perhaps load in the replies via AJAX or something like that?

If so that would some thing along these lines for the root level comments:


SELECT
      c.id,
      c.uid,
      c.text comment,
      c.`date`,
      COUNT(r.id) replies
  FROM
      comments_tbl c
  LEFT OUTER
  JOIN
      replies_tbl r
    ON
      c.id = r.comment_parent_id
 GROUP
    BY
      c.id
 ORDER
    BY
      c.`date` DESC

Than for children would be something like:


SELECT
      c.id,
      c.uid,
      c.text comment,
      c.`date`,
      COUNT(r2.id) replies
  FROM
      comments_tbl c
  INNER JOIN
  JOIN
      replies_tbl r
    ON
      c.id = r.comment_id
   LEFT OUTER
   JOIN
      replies_tbl r2
    ON
      c.id = r2.comment_parent_id
 WHERE
      r.comment_parent_id = %comment_id%
 GROUP
    BY
      c.id
 ORDER
    BY
      c.`date` DESC

Replace %comment_id% is the int value of the parent comment to fetch children for.

For both queries within the application logic use the replies column to determine whether there are replies. When replies is 0 there aren’t any replies to the comment.

9

hi again , how if i want the same result where text like ‘%str%’

i have tried


SELECT c.id 
     , c.uid 
     , c.text 
     , COALESCE(r.last, c.date) as sortdate
  FROM comments_tbl AS c
LEFT OUTER
  JOIN ( SELECT replies_tbl.comment_parent_id
              , MAX(comments_tbl.date) AS last
           FROM replies_tbl
         INNER
           JOIN comments_tbl
             ON comments_tbl.id = replies_tbl.comment_id
         GROUP
             BY replies_tbl.comment_parent_id ) AS r
    ON r.comment_parent_id = c.id
 WHERE c.id NOT IN
       ( SELECT comment_id FROM replies_tbl )    and  c.text like '%str%'
ORDER
    BY sortdate DESC

the problem is , if comments has not the ‘str’ in the text column but the replies has the ‘str’ in the text , the comments will not be retrieved
can you help me please ?
thanks again