Counting total replies to a post

[Erik_Fischer]

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

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

If you don't understand the logic, let's take post id 4. Post id 4 is a reply to post id 3 which is a reply to post id 2 which is a reply to post id 1. That means that there are a total of 3 replies to post id 1.

 

Using the PHP values $post_id, $reply_to, and $parent_id, how would I create a query that would increment the total number of replies when someone makes a new reply to a post? So for example, if I replied to post id 4, how would I increment all of the necessary numbers so that it would look like this:

post_id | reply_to | parent_id | total_replies
   1         1           1            4
   2         1           1            3
   3         2           1            2
   4         3           1            1
   5         4           1            0 (this is the new reply)

Thanks.

Edited December 11, 2013 by Erik_Fischer

[Zane]

You should never store aggregate/calculated data in a table like that, it's an absolute waste of space and energy.

 

Imagine your elder sent you to the store to get groceries and handed you 100 dollars. Also imagine that you must go to multiple stores to pick everything up, plus you may need gas money, and you might even want a bottle water too.

 

Would you continue to calculate what you have spent after every transaction? Would you seriously want to continue adding the totals of the receipts and writing down the sum every time? When you go to pay the cashier(s), are you planning to pull all of those receipts out of your pocket, add them up and see whether or not it is greater than 100? Or, would you simply count what money you have left in your pocket? The quickest possible way would be the latter.

 

Storing your total_replies would be known as storing duplicate and redundant data. Every time a reply is made, you will have to run one extra unnecessary query to update total_replies.

 

Furthermore, you logic for the post_id to reply_to does not make sense. According to your data, Post ID 1 is a reply to Post ID 1 and that cannot be. That would be like saying you are your own parent; that your mother and father are literally yourself.

 

In order to do what you are describing would require an endless amount of self joins, if I'm not mistaken.

 

Ideally, the post_id should be a primary key that auto increments. Your next column would be the thread id.

and you could query like so

 

SELECT p.post_id from posts p inner join threads t on t.thread_id = p.thread_id where p.thread_id = X

Where X would be, of course, the thread id. This would give you a table of posts inside thread X. And since your post_id is auto incremented, you can assume that the lowest post_id returned from the above query is the first post in that thread. So you could safely add an ORDER BY p.post_id to receive a list of posts for thread X in ascending or descending order.

[Erik_Fischer]

You should never store aggregate/calculated data in a table like that, it's an absolute waste of space and energy.

 

Imagine your elder sent you to the store to get groceries and handed you 100 dollars. Also imagine that you must go to multiple stores to pick everything up, plus you may need gas money, and you might even want a bottle water too.

 

Would you continue to calculate what you have spent after every transaction? Would you seriously want to continue adding the totals of the receipts and writing down the sum every time? When you go to pay the cashier(s), are you planning to pull all of those receipts out of your pocket, add them up and see whether or not it is greater than 100? Or, would you simply count what money you have left in your pocket? The quickest possible way would be the latter.

 

Storing your total_replies would be known as storing duplicate and redundant data. Every time a reply is made, you will have to run one extra unnecessary query to update total_replies.

 

Furthermore, you logic for the post_id to reply_to does not make sense. According to your data, Post ID 1 is a reply to Post ID 1 and that cannot be. That would be like saying you are your own parent; that your mother and father are literally yourself.

 

In order to do what you are describing would require an endless amount of self joins, if I'm not mistaken.

 

Ideally, the post_id should be a primary key that auto increments. Your next column would be the thread id.

and you could query like so

 

SELECT p.post_id from posts p inner join threads t on t.thread_id = p.thread_id where p.thread_id = X

Where X would be, of course, the thread id. This would give you a table of posts inside thread X. And since your post_id is auto incremented, you can assume that the lowest post_id returned from the above query is the first post in that thread. So you could safely add an ORDER BY p.post_id to receive a list of posts for thread X in ascending or descending order.

 

 

Thanks for the reply, however, if I'm correct in saying that "thread_id" is basically my "parent_id", right?

 

If so, that would only return the total replies to the parent_id (thread_id), whereas I need it to show the total replies to the parent_id, and each individual reply made to that post.

 

For example, if we have a post (let's say an id of 1). If I reply to that post, then the total number of replies to it will be 1. If I reply to the reply of that post, then the total number of replies to the post will be 2 and the total number of replies to the reply will be 1. Furthermore, if I reply to the reply of the reply of that post, then the total number of replies to the post will be 3, the total number of replies to the reply will be 2, and the total number of replies to the reply of the reply will be 1. And so on.

 

 

What you've suggested only accounts for the parent_id's (thread_id's) total replies. :/

Edited December 11, 2013 by Erik_Fischer

[Erik_Fischer]

I've added a diagram to make it more clear how the system works.

[Zane]

Read up on Nested Sets, that seems to be what you're after. I must sleep now.

http://www.evanpetersen.com/item/nested-sets.html

http://www.slideshare.net/billkarwin/models-for-hierarchical-data

[Erik_Fischer]

Read up on Nested Sets, that seems to be what you're after. I must sleep now.

http://www.evanpetersen.com/item/nested-sets.html

http://www.slideshare.net/billkarwin/models-for-hierarchical-data

 

So I've done some researching on hierarchical data. My question if, since this is a comment system and there can be thousands of replies to a post (meaning trees that could have a height of 1000+), how slow would things become?

 

Also, with nested sets, wouldn't that mean I would have to update the entire tree's left and right values every time a new reply is made?

Edited December 13, 2013 by Erik_Fischer