SitePoint Sponsor

User Tag List

Results 1 to 8 of 8

Thread: comments query

  1. #1
    SitePoint Member
    Join Date
    May 2013
    Posts
    15
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    comments query

    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
    id uid text date

    replies_tbl
    id comment_id comment_parent_id

    how please ?

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    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?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    May 2013
    Posts
    15
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    its should be in one table , but this is the case now i cant change it really now

    id uid text date
    1 003 text1 2011-1-26 17:58:13
    2 004 text2 2011-2-26 17:58:13
    3 003 text1 2011-3-26 17:58:13
    4 006 text1 2011-4-26 17:58:13
    5 006 text1 2011-5-26 17:58:13
    6 006 text6 2011-6-26 17:58:13
    8 011 text11 2011-7-26 17:58:13
    9 009 text9 2011-8-26 17:58:13
    10 017 text17 2011-9-20 17:58:13
    11 011 text11 2011-10-21 17:58:13

    id comment_id comment_parent_id
    1 2 1
    2 3 1
    3 5 4
    4 6 4
    5 11 4


    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. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by mnask View Post
    i cant change it really now
    oh, okay then
    Code:
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Member
    Join Date
    May 2013
    Posts
    15
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    not working fine , its displaying replies too

  6. #6
    SitePoint Member
    Join Date
    May 2013
    Posts
    15
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    the replies should not be in the query result , because i will not display them , they will be as part of the comments

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by mnask View Post
    the result should be

    comment_id
    4 -> has new reply
    10
    9
    8
    7
    1
    no, that's not quite right -- there is no comment 7 !!
    Code:
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,146
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)
    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:

    Code SQL:
    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:

    Code SQL:
    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.
    The only code I hate more than my own is everyone else's.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •