PostgreSQL - Fetch only a specified limit of related rows but with a number indicating the total cou-CodePudding

Basically I have 2 models like so:

Comments:

 ---- -------- ---------- ----------------- 
| id | userId | parentId | text            |
 ---- -------- ---------- ----------------- 
| 2  | 5      | 1        | Beautiful photo |
 ---- -------- ---------- ----------------- 
| 3  | 2      | 2        | Thanks Jeff.    |
 ---- -------- ---------- ----------------- 
| 4  | 7      | 2        | Thank you, Jeff.|
 ---- -------- ---------- -----------------

This table is designed to handle threads. Each parentId is a comment itself.

And CommentLikes:

 ---- -------- ----------- 
| id | userId | commentId |
 ---- -------- ----------- 
| 1  | 2      | 2         |
 ---- -------- ----------- 
| 2  | 7      | 2         |
 ---- -------- ----------- 
| 3  | 7      | 3         |
 ---- -------- -----------

What I'm trying to achieve is an SQL query that will perform the following (given the parameter parentId): Get a limit of 10 replies that belong to parentId. With each reply, I need a count indicating the total number of replies to that reply and another count indicating the total number of likes given to that reply.

Sample input #1: /replies/1

Expected output:

[{
id: 2,
userId: 5,
parentId: 1,
text: 'Beautiful photo',
likeCount: 2,
replyCount: 2
}]

Sample input #2: /replies/2

Expected output:

[
{
id: 2,
userId: 2,
parentId: 2,
text: 'Thanks Jeff.'
replyCount: 0,
likeCount: 1
},
{
id: 3,
userId: 7,
parentId: 2,
text: 'Thank you, Jeff.'
replyCount: 0,
likeCount: 0
}
]

I'm trying to use Sequelize for my case but it seems to only over-complicate things so any raw SQL query will do.

Thank you in advance.

CodePudding user response：

what about something like this

SELECT *,
(SELECT COUNT(*) FROM comment_likes WHERE comments.id =comment_likes."commentId")  AS likecount,
(SELECT COUNT(*) FROM comments AS c WHERE c."parentId" = comments.id) AS commentcount
FROM  comments
WHERE comments."parentId"=2