Mysql query to get posts with recent comments - including comment count

Question

I need to

1. Get posts where someone has commented in the past 15 days
2. Sort by comment count
3. Return the post id along with the total number of comments for relevant posts

This is what I have:

SELECT post.id, COUNT(DISTINCT postcomments.id) as comments
FROM posts
LEFT JOIN postcomments on postcomments.postid = post.id
WHERE postcomment.posted_at >= DATE_SUB(NOW(), INTERVAL 15 DAY)
GROUP BY post.id
HAVING comments > 0
ORDER BY comments DESC

The problem with my query is that my comment count only includes comments posted in the past 15 days, whereas I want the total number of comments for the posts. How can I achieve this in one query?

Answer 1

SELECT post.id, COUNT(DISTINCT postcomments.id) as comments
FROM posts
JOIN postcomments on postcomments.postid = post.id
GROUP BY post.id
HAVING MAX(postcomment.posted_at) >= DATE_SUB(NOW(), INTERVAL 15 DAY)
ORDER BY comments DESC

Answer 2

Perhaps you want to use conditional aggregation here:

SELECT
    p.id,
    SUM(pc.posted_at >= DATE_SUB(NOW(), INTERVAL 15 DAY)) AS comments
FROM posts p
LEFT JOIN postcomments pc ON pc.postid = p.id
GROUP BY
    p.id
HAVING
    COUNT(*) > 0
ORDER BY
    comments DESC;

The comments count should still be the same as before, except that now we assert the total number of comments, rather than just asserting comments within the last 15 days.