CODEWITHSUNDEEP
phpmysqlcodeigniterdatetimeaggregate-functions
enemetch
enemetch

Reputation: 522

SQL to get Latest comment date for a post - Discussion Forum

I am working on a discussion forum website. When you open the forum, a webservice fetches all the active posts.

Title, Count of Comments, Recent Activity (Date of latest comment), Posted By, Status.

I am able to get all of the above data except Recent Activity. 

SELECT 
  `post`.`id`, 
  `post`.`title`,
  `post`.`description`, 
  `post`.`created`, 
  COUNT(`comment_text`) AS `num_comments`,
  `users`.`name`, 
  `users`.`nick_name`, 
  `users`.`profile_picture` 
FROM `post`
JOIN `users` ON `users`.`id` = `post`.`user_id` 
JOIN `posttype` ON `posttype`.`id` = `post`.`post_type_id` 
LEFT JOIN `comments` ON `comments`.`post_id` = `post`.`id` 
WHERE `post_type` = 'Discussion'
GROUP BY `post`.`id`

How do I modify this SQL to get latest comment date?

The comment_date is in comments table

Upvotes: 3

Views: 529

Answers (1)

O. Jones
O. Jones

Reputation: 108776

Use MAX(comment_date) the same way you're using COUNT(comment_text) to get the latest date over all the comments in each GROUP BY post.id group.

Be careful, you're exploiting the MySQL non-standard extension to GROUP BY. In your case, the extension may cause your result set to contain information from the users table that you can't predict. Read this. http://dev.mysql.com/doc/refman/5.6/en/group-by-handling.html

Upvotes: 1

Related Questions