使用knex在MySQL节点中进行多次计数和左联接

Question

我正在尝试查询帖子，并使用"count"来获取要显示的评论和点赞总数。我的查询看起来像这个

const posts = await knex
.from("posts")
.select("posts.id as id", "posts.text", "posts.user_id")
.leftJoin("comments", "comments.post_id", "posts.id")
.count("comments.post_id as comments")
.leftJoin("likes", "likes.post_id", "posts.id")
.count("likes.post_id as likes")
.groupBy("posts.id");
res.send(posts);

然而，如果我排除评论或点赞并这样做，我会得到不同的结果：

const posts = await knex
.from("posts")
.select("posts.id as id", "posts.text", "posts.user_id")
.leftJoin("comments", "comments.post_id", "posts.id")
.count("comments.post_id as comments")
.groupBy("posts.id");
res.send(posts);

我觉得我做错了什么。链接多个"count"one_answers"leftJoins"的正确方法是什么？

Answer 1

首先从SQL查询开始，然后将其转换为Knex。

正如@nbk所说，当你在最终结果中加入评论时，你会收到每个评论的一行。

一个选项是在select中使用子查询，查询将看起来像：

Select posts.id as id, posts.text, posts.user_id, 
(Select count(*) from comments where comments.post_id=posts.id) as comments,
(Select count(*) from likes where likes.post_id=posts.id) as likes,
From posts;

此查询可以转换为Knex:

const posts = await knex
.from('posts')
.select(
'posts.id as id',
'posts.text',
'posts.user_id',
knex('comments')
.count('*')
.whereRaw('?? = ??', ['comments.post_id', 'posts.id'])
.as('comments'),
knex('likes').count('*').whereRaw('?? = ??', ['likes.post_id', 'posts.id']).as('likes')
);