在父记录属性列表中包含子记录count，而不获取所有子记录

Question

我已经为序列化实验设置了一个非常基本的小型游乐场，以了解它在关联记录获取时的行为。但是我不能达到期望的输出。

<<p>可重复的代码/strong>

const Sequelize = require('sequelize');
const { fn } = Sequelize;
const sequelize = new Sequelize('playground', 'postgres', 'postgres', {
host: 'localhost',
dialect: 'postgres',
pool: {
max: 9,
min: 0,
idle: 10000
}
});
const Posts = sequelize.define('posts', {
id: {
type: Sequelize.INTEGER,
primaryKey: true
},
title: {
type: Sequelize.STRING
},
content: {
type: Sequelize.STRING
}
}, 
{
freezeTableName: true,
timestamps: false
}
);
const Comments = sequelize.define('comments', {
id: {
type: Sequelize.INTEGER,
primaryKey: true
},
postId: {
type: Sequelize.INTEGER,
references: {
model: 'posts',
key: 'id'
}
},
comment: {
type: Sequelize.STRING
}
}, 
{
freezeTableName: true,
timestamps: false
}
);
Posts.hasMany(Comments, {
foreignKey: 'postId'
})

let criteria = {
group: ['posts.id', 'comments.id'],
attributes: [
[fn('count', 'comments.id'), 'TotalComments']
],
include: [
{
model: Comments,
}
]
}

Posts.findAll(criteria).then(posts =>{
console.dir(JSON.parse(JSON.stringify(posts)), {depth: null, colors: true});
});

问题遇到

问题是直接的，我想在父属性中添加子记录计数，例如返回的每个帖子中所有评论的计数，但我得到的输出是返回一个固定计数，这是1。我可以简单地迭代所有的帖子，并通过读取评论的总长度来设置TotalComments，但我想使用序列化查询来做到这一点，因为这样我可以应用一些过滤器，例如评论在6到10之间的帖子

输出显示

[
{
id: 101,
title: 'The revolution in brain',
TotalComments: '1', //this count is not matched with below included comments count
comments: [
{ id: 15, postId: 101, comment: 'It helped me a lot' },
{ id: 13, postId: 101, comment: 'very disapointing' },
{ id: 12, postId: 101, comment: 'welldone' },
{ id: 14, postId: 101, comment: 'This post sucks' }
]
},
{
id: 102,
title: 'Making your hands dirty on Java',
TotalComments: '1',
comments: []
},
{
id: 100,
title: 'Earlier models of medical surgery',
TotalComments: '1',
comments: [
{ id: 10, postId: 100, comment: 'Appreciated your work' },
{ id: 11, postId: 100, comment: 'good' }
]
}
]

输出所需的

[
{
id: 101,
title: 'The revolution in brain',
TotalComments: 4,
comments: [
{ id: 15, postId: 101, comment: 'It helped me a lot' },
{ id: 13, postId: 101, comment: 'very disapointing' },
{ id: 12, postId: 101, comment: 'welldone' },
{ id: 14, postId: 101, comment: 'This post sucks' }
]
},
{
id: 102,
title: 'Making your hands dirty on Java',
TotalComments: 0,
comments: []
},
{
id: 100,
title: 'Earlier models of medical surgery',
TotalComments: 2,
comments: [
{ id: 10, postId: 100, comment: 'Appreciated your work' },
{ id: 11, postId: 100, comment: 'good' }
]
}
]

生成的SQL

SELECT "posts"."id", "posts"."title", count('comments.id') AS "TotalComments", "comments"."id" AS "comments.id", "comments"."postId" AS "comments.postId", "comments"."comment" AS "comments.comment" FROM "posts" AS "posts" LEFT OUTER JOIN "comments" AS "comments" ON "posts"."id" = "comments"."postId" GROUP BY "posts"."id", "comments"."id";

Answer 1

经过大量搜索，我自己解决了这个问题，我希望它能帮助其他人，如果他们遇到类似的问题，基本上我使用序列化文字插入一些原始查询来解决问题，修改的查询标准示例如下

let criteria = {
group: ['posts.id', 'comments.id'],
attributes: [
'id',
'title',
[literal('(select count(comments.id) from comments where "comments"."postId" = "posts"."id")'), 'TotalComments']
],
include: [
{
model: Comments,
}
]
}

Posts.findAll(criteria).then(posts =>{
console.dir(JSON.parse(JSON.stringify(posts)), {depth: null, colors: true});
});