CODEWITHSUNDEEP
mysql
Amy Neville
Amy Neville

Reputation: 10611

Mysql forum - get number of replies

I'm making a very simple forum with one table called forum_posts. I store both the replies and the posts in that same table because I've found that works pretty well for comments system I made before.

If the post is a reply, it has a reply_id that is the post_id of the post it is replying to. If it is a 'root post' so to speak, it has a 0 for the reply_id.

I already have the number of views. But I'd like to get the number of replies for each record in the results.

How would I do that?

SELECT a.account_id, a.store_name, p.post_id, p.post_title, p.post_text, p.views, p.creation_timestamp, p.update_timestamp
FROM forum_posts AS p
INNER JOIN accounts AS a
ON p.account_id = a.account_id
WHERE p.reply_id > 0

As you can probably guess, I'm making the forum listings where people choose a forum post to go and view.

Upvotes: 0

Views: 127

Answers (2)

striving_coder
striving_coder

Reputation: 798

SELECT account_id, store_name, post_id, post_title, post_text, views, creation_timestamp, update_timestamp, IF(reply_id IS NOT NULL, replies, 0)
FROM (
    SELECT a.account_id, a.store_name, p.post_id, p.post_title, p.post_text, p.views, p.creation_timestamp, p.update_timestamp, r.reply_id, COUNT(*) as replies
    FROM forum_posts AS p
    INNER JOIN accounts AS a
    ON p.account_id = a.account_id
    LEFT JOIN forum_posts AS r
    ON p.post_id = r.reply_id
    WHERE p.reply_id = 0
    GROUP BY p.post_id
) AS sq

It also seems that you need p.reply_id = 0 if you want to show only "root" posts (forum threads) rather than replies.

Outer query makes sure that the posts with no replies (which are still returned in the inner query) are printed with the number of replies 0 rather than 1 (which would be incorrect obviously).

Upvotes: 1

AndySavage
AndySavage

Reputation: 1769

You will need to join posts against itself and count it to get the number of replies. This isn't particularly efficient over millions of forum posts - most forums denormalize this and maintain a post count attribute separately.

However, in keeping with what you have, something like (untested)...

SELECT a.account_id, a.store_name, cp.* FROM
(
    SELECT p.post_id, p.post_title, p.post_text, p.views, p.creation_timestamp, 
        p.update_timestamp,  p.account_id, (COUNT(*) - 1) as replies
    FROM forum_posts AS p
    LEFT JOIN forum_posts AS p1 ON p1.reply_id > 0 AND p1.reply_id = p.post_id
    GROUP BY p.post_id
)
AS cp
INNER JOIN accounts AS a ON cp.account_id = a.account_id
WHERE cp.replies > 0

The end WHERE is optional. I just copied it from your first query. It's also worth noting that this is MySQL specific as it uses the GROUP BY without a full list of non-aggregated columns (but you tagged your question as MySQL so no problem).

Upvotes: 1

Related Questions