I have a MySQL query which should count the amount of replies to your comments with relations to other columns.
I try to "relate" two columns together but can’t figure out how to create a correct query.
The table somewhat looks like this:
|4||i didn’t like it so much||4||0|
|7||why is that?||5||4|
So to know how many replies you have, we need to relate replied_to and comment_id. But also we need to determine the relation between the comment_id and to whom it belongs to.
Let’s assume that I am a user with author_id 4 and two people replied to my comment which are the comment_id 5 and 6. The query should return 2.
I’ve tried something like this (this is an incorrect query, since you can’t do something like comment_id.author_id = 4):
SELECT COUNT(*) FROM comments WHERE replied_to = comment_id AND comment_id.author_id = 4;
How to approach this kind of query, or is the table structure incorrectly done and should be in another table instead?
Using a self-join can help in situations like this.
SELECT COUNT(*) AS count FROM comments AS post JOIN comments AS reply ON reply.replied_to = post.comment_id WHERE post.author_id = 4;