How to write query (sql server) for loading comments & replies like facebook?

Question

I had comment table: (using for both comment & reply)

-commentId
-parentId
-message

parentId=0: comment
parentId != 0: reply

Like facebook, i want to query (sql server) to get comments + replies. But with replies, i just want to get 3 latest replies. and at the website, i will display "show all replies" to fetch by ajax call.

So, how can i write this query: For example: https://i.stack.imgur.com/mASRC.png

Any help? please!!!

score 1 · Answer 1 · answered Jul 07 '11 at 04:40

For any hierarchical structure you need a recursive query. Because if you don't limit the number of replies, you don't know how many levels of reply is stored in your table. Your table is like these tables:

    CategoryId & CategoryTitle & ParentCategoryId

or

    EmployeeId & EmployeeName & ManagerId

But in none of these tables, you don't know how many levels exist in your table. So, you have to write a recursive query.

score 1 · Answer 2 · edited Nov 13 '18 at 23:37

https://web.archive.org/web/20080206204801/http://sqlserver2000.databases.aspfaq.com:80/how-do-i-limit-the-number-of-rows-returned-in-my-resultset.html

This article looks like it might be helpful in constraining the number of rows in a result set.

Have you tried something like:

SELECT TOP 3 commentID, parentId, message
FROM commentTable
WHERE parentId IN(0,1)
ORDER BY message DESC;

If you have the power to control the layout of your database, you might want to break your table apart into two tables on individual themes: one for messages, and one for replies. You could then add a date-time field to the replies table and select the TOP 3 rows from it, based off of the posting date, with order desc.

Mikael Eriksson · Accepted Answer · 2011-07-07T06:11:28.890

declare @Comment table(commentId int, parentId int, message varchar(20))

insert into @Comment 
select 1, 0,'my comment 1' union all
select 2, 0,'mu comment 2' union all
select 3, 1,'reply 1.1'    union all
select 4, 1,'reply 1.2'    union all
select 5, 1,'reply 1.3'    union all
select 6, 1,'reply 1.4'    union all
select 7, 1,'reply 1.5'    union all
select 8, 2,'reply 2.1'    union all
select 9, 2,'reply 2.2'    union all
select 10,2,'reply 2.3'    union all
select 11,2,'reply 2.4'

;with C as
(
  select commentId,
         parentId,
         message,
         row_number() over(partition by parentId order by commentId desc) as rn
  from @Comment
)
select C.commentId,
       C.parentId,
       C.message
from C
where C.parentId = 0 or C.rn <= 3
order by C.commentId

Result:

commentId   parentId    message
----------- ----------- --------------------
1           0           my comment 1
2           0           mu comment 2
5           1           reply 1.3
6           1           reply 1.4
7           1           reply 1.5
9           2           reply 2.2
10          2           reply 2.3
11          2           reply 2.4

but.... how can you support pagination (pageNumber, pageSize) to it. Thanks you very much!!!!!!!!!!!!!!!!!!!! — vNext, Jul 07 '11 at 07:23
vNext - Look here for how you can do pagination in SQL Server. http://stackoverflow.com/questions/109232/what-is-the-best-way-to-paginate-results-in-ms-sqlserver — Mikael Eriksson, Jul 07 '11 at 08:18

How to write query (sql server) for loading comments & replies like facebook?

3 Answers3