Comments Parent-Child query with indentation
- by poldoj
I've been trying to retrieve comments to articles in a pretty common blog fashion way. Here's my sample code:
-- ----------------------------
-- Sample Table structure for [dbo].[Comments]
-- ----------------------------
CREATE TABLE [dbo].[Comments] (
[CommentID] int NOT NULL ,
[AddedDate] datetime NOT NULL ,
[AddedBy] nvarchar(256) NOT NULL ,
[ArticleID] int NOT NULL ,
[Body] nvarchar(4000) NOT NULL ,
[parentCommentID] int NULL
)
GO
-- ----------------------------
-- Sample Records of Comments
-- ----------------------------
INSERT INTO [dbo].[Comments] ([CommentID], [AddedDate], [AddedBy], [ArticleID], [Body], [parentCommentID]) VALUES (N'1', N'2011-11-26 23:18:07.000', N'user', N'1', N'body', null);
GO
INSERT INTO [dbo].[Comments] ([CommentID], [AddedDate], [AddedBy], [ArticleID], [Body], [parentCommentID]) VALUES (N'2', N'2011-11-26 23:18:50.000', N'user', N'2', N'body', null);
GO
INSERT INTO [dbo].[Comments] ([CommentID], [AddedDate], [AddedBy], [ArticleID], [Body], [parentCommentID]) VALUES (N'3', N'2011-11-26 23:19:09.000', N'user', N'1', N'body', null);
GO
INSERT INTO [dbo].[Comments] ([CommentID], [AddedDate], [AddedBy], [ArticleID], [Body], [parentCommentID]) VALUES (N'4', N'2011-11-26 23:19:46.000', N'user', N'3', N'body', null);
GO
INSERT INTO [dbo].[Comments] ([CommentID], [AddedDate], [AddedBy], [ArticleID], [Body], [parentCommentID]) VALUES (N'5', N'2011-11-26 23:20:16.000', N'user', N'1', N'body', N'1');
GO
INSERT INTO [dbo].[Comments] ([CommentID], [AddedDate], [AddedBy], [ArticleID], [Body], [parentCommentID]) VALUES (N'6', N'2011-11-26 23:20:42.000', N'user', N'1', N'body', N'1');
GO
INSERT INTO [dbo].[Comments] ([CommentID], [AddedDate], [AddedBy], [ArticleID], [Body], [parentCommentID]) VALUES (N'7', N'2011-11-26 23:21:25.000', N'user', N'1', N'body', N'6');
GO
-- ----------------------------
-- Indexes structure for table Comments
-- ----------------------------
-- ----------------------------
-- Primary Key structure for table [dbo].[Comments]
-- ----------------------------
ALTER TABLE [dbo].[Comments] ADD PRIMARY KEY ([CommentID])
GO
-- ----------------------------
-- Foreign Key structure for table [dbo].[Comments]
-- ----------------------------
ALTER TABLE [dbo].[Comments] ADD FOREIGN KEY ([parentCommentID]) REFERENCES [dbo]. [Comments] ([CommentID]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
I thought I could use a CTE query to do the job like this:
WITH CommentsCTE(CommentID, AddedDate, AddedBy, ArticleID, Body, parentCommentID, lvl, sortcol)
AS
(
SELECT CommentID, AddedDate, AddedBy, ArticleID, Body, parentCommentID, 0, cast(CommentID as varbinary(max))
FROM Comments
UNION ALL
SELECT P.CommentID, P.AddedDate, P.AddedBy, P.ArticleID, P.Body, P.parentCommentID, PP.lvl+1,
CAST(sortcol + CAST(P.CommentID AS BINARY(4)) AS VARBINARY(max))
FROM Comments AS P
JOIN CommentsCTE AS PP
ON P.parentCommentID = PP.CommentID
)
SELECT
REPLICATE('--', lvl)
+ right('>',lvl)+ AddedBy
+ ' :: '
+ Body,
CommentID,
parentCommentID,
lvl
FROM CommentsCTE
WHERE ArticleID = 1
order by sortcol
go
but the results have been very disappointing so far, and after days of tweaking I decided to ask for help. I was looking for a method to display hierarchical comments to articles like it happens in blogs.
[edit]
The problem with this query is that I get duplicates because I couldn't figure out how to properly select the ArticleID which I want comments from to display. I'm also looking for a method that sorts children entries by date within a same level.
An example of what I'm trying to accomplish could be something like:
(ArticleID[post retrieved])
-------------------------
-------------------------
(Comments[related to the article id above])
first comment[no parent]
--[first child to first comment]
--[second child to first comment]
----[first child to second child comment to first comment]
--[third child to first comment]
----[first child to third child comment to first comment]
------[(recursive child): first child to first child to third child comment to first comment]
------[(recursive child): second child to first child to third child comment to first comment]
second comment[no parent]
third comment[no parent]
--[first child to third comment]
I kinda got myself lost in all this mess...I appreciate any help or simpler ways to get this working. Thanks
