How do I write this query more efficiently?

Question

On a profile website of mine users are able to comment, and comment on a comment (1 level like facebook has). I'm implementing some pagination because there can be thousands of comments on 1 profile. The pagination works, but, since there are child comments, a simple top n query breaks the conversation. I only want to paginate over the parent comments, not the childs.

The table 'comments' which has:

- commentID
- commentText
- parentCommentID
- commentOnUserID

The problem here is that I want to paginate only over comments that are parents (parentCommentID = 0). So I write a query like:

select * from Comments c
where c.parentCommentID = 0 
and c.commentOnUserID = 65939 

(I left out the actual pagination query because it's not relevant)

but I also want to load the childs of all of these comments, a child is also a comment but then with parentCommentID = some commentID:

select * from comments c
where c.parentCommentID in ( * get the commentId's from the previous query)
and c.commentOnUserID = 65939

Is there a way to efficiently combine the two in 1 query?

Answer 1

declare @T table(commentID int,
                 commentText varchar(max),
                 parentCommentID int,
                 commentOnUserID int)

insert into @T values
(1, 'Comment 1', 0, 1),
(2, 'Comment 2', 0, 1),
(3, 'Comment 3', 0, 1),
(4, 'Comment 4 sub 1', 1, 1),
(5, 'Comment 5 sub 1', 1, 1),
(6, 'Comment 6 sub 1', 1, 1),
(7, 'Comment 1 sub 2', 2, 1),
(8, 'Comment 1 sub 2', 2, 1),
(9, 'Comment 1 sub 3', 3, 1)

declare @UserID int = 1

;with cte as
(
  select
    T.commentID,
    T.CommentText,
    row_number() over(order by commentID) as rn
  from @T as T
  where
    T.parentCommentID = 0 and
    T.commentOnUserID = @UserID
  union all
  select   
    T.commentID,
    T.CommentText,
    C.rn
  from @T as T
    inner join cte as C
      on T.parentCommentID = C.commentID
)
select *
from cte
where rn between 1 and 2 -- use rn for pagination
order by rn, commentID

Result

commentID   parentCommentID CommentText          rn
----------- --------------- -------------------- --------------------
1           0               Comment 1            1
4           1               Comment 4 sub 1      1
5           1               Comment 5 sub 1      1
6           1               Comment 6 sub 1      1
2           0               Comment 2            2
7           2               Comment 1 sub 2      2
8           2               Comment 1 sub 2      2