增加对帖子的总回复

In my database, I have the following structure for posts:

post_id | reply_to | parent_id
   1         1           1
   2         1           1
   3         2           1
   4         3           1

So in this case, post_id 1 is the main post, while post_id 2 is a reply to post_id 1, post_id 3 is a reply to post_id 2, and post_id is a reply to post_id 3.

What I want to do is add another column that keeps track of the total number of replies to a post. So in the end, the table would look something like this:

post_id | reply_to | parent_id | total_replies
   1         1           1            3
   2         1           1            2
   3         2           1            1
   4         3           1            0

How/what would the query look like if I wanted to update the total number of replies?

Thanks. :)

Something like:

update posts p set total_replies = (select count(t.post_id) from 
posts t where t.reply_to = p.post_id)

If you want to just do a simple calculation per post, you do this:

UPDATE  posts 
        LEFT JOIN
        (
            SELECT post_id , (SELECT count(*) from posts p0 where p.post_id = p0.reply_to) as total_replies
            FROM posts p 
        ) p2 ON posts.post_id  = p2.post_id 
SET     posts.total_replies =p2.total_replies;

See it working there:http://sqlfiddle.com/#!2/868c6/1

Now, what you want is to do a recursive read, to calculate replies until it reach the top post. The worse way is to calculate this at quering data, so make it just when you save a new post, you can do this at PHP, or create a Store Procedure/Function in database, it'll be something like:

$total_reply = 0;

function calculateTotalReply($postId)
{

    //Make the simple sum as I did above
   $total_reply = $sum;

    //Check if is the top post
    if(!$isTheTopPost)
    {
         calculateTotalReply($parentPost);
    }

}

So, as you can see, it'll call himself until reach the top post, in the end at $total_reply you'll have your desired sum.