i have a table for Comments (ID, UserName, UserEmail, CommentBody, PostID)
and another table for its Replies (ID, UserName, UserEmail, SubCommentBody, ParentCommentID)
in this case i have only one nested comment/reply and each comment can have more than one replies but replies can't have any replies
for example:
Comments Table
ID UserName UserEmail CommentBody PostID
-- -------- --------- ----------- ------
1 Dave a@gmail.com hello... 148
2 Alex b@gmail.com hi.... 205
3 John c@gmail.com something.. 205
Replies Table
ID UserName UserEmail SubCommentBody ParentCommentID
-- -------- --------- ----------- ---------------
1 Jimmy g@gmail.com BlaBla... 1
2 Ben h@gmail.com Fine.... 1
3 Jerry m@gmail.com something.. 2
how i write a query to get comments and its replies? i have no idea :)
No need to differ comments and replies, since they serve the same purpose and structure (name, body, etc).
Use only Replies (ID, UserName, UserEmail, Body, Date, ParentReplyId, PostId).
No parent means a root comment. If it has a parent, place the reply under its parent.
Use Date to sort the replies.
To load all replies for a given reply (hierarchical structure), use a recursive query. For example, load all replies of Id=13 reply, in SQL Server it's:
;WITH x AS
(
-- anchor:
SELECT Id, UserName, Body, ParentReplyId
FROM Replies WHERE ParentReplyId = 13
UNION ALL
-- recursive:
SELECT t.Id, t.FirstName, t.Body, t.ParentReplyId
FROM x INNER JOIN Replies AS t
ON t.ParentReplyId = x.Id
)
SELECT Id, FirstName, Body, ParentReplyId, FROM x
To load all replies for a given post, no need for recursive query, just load all replies linked to a given PostId:
SELECT * FROM Replies WHERE PostId = 100