计算顺序返回中的关系1而不是0

Question

我有下面的sequelize测试用例，我试图返回模型上关系的计数。

test('Tag with file count', async () => {
const tagOne = await db.Tag.create({
label: 'One'
})
const tagTwo = await db.Tag.create({
label: 'Two'
})
const tagThree = await db.Tag.create({
label: 'Three'
})
const tagFour = await db.Tag.create({
label: 'Four'
})
const tagFive = await db.Tag.create({
label: 'Five'
})
const fileOne = await db.File.create()
const fileTwo = await db.File.create()
await fileOne.setTags([tagOne, tagTwo, tagFour])
await fileTwo.setTags([tagOne, tagTwo, tagThree])
const output = await db.Tag.findAll({
attributes: {
include: [
[Sequelize.fn('COUNT', 'Files.id'), 'fileCount']
]
},
include: [
{
model: db.File,
as: 'files',
attributes: [],
duplicate: false
}
],
group: 'Tag.id',
order: [
[Sequelize.literal('`fileCount`'), 'DESC']
]
})
expect(output.length).toBe(5)
expect(output.find(tag => tag.label === tagOne.label).dataValues.fileCount).toBe(2)
expect(output.find(tag => tag.label === tagTwo.label).dataValues.fileCount).toBe(2)
expect(output.find(tag => tag.label === tagThree.label).dataValues.fileCount).toBe(1)
expect(output.find(tag => tag.label === tagFour.label).dataValues.fileCount).toBe(1)
expect(output.find(tag => tag.label === tagFive.label).dataValues.fileCount).toBe(0)
})

对于tagFive，我希望列fileCount为0，但它返回1。对于其他人来说，它似乎返回了正确的文件计数。

findAll中是否缺少选项或存在错误？

编辑

我得到以下查询生成的

Executing (default): SELECT `Tag`.`id`, 
`Tag`.`label`, 
`Tag`.`slug`, 
`Tag`.`createdAt`, 
`Tag`.`updatedAt`, COUNT('Files.id') AS `fileCount`, 
`files->FileTags`.`createdAt` AS `files.FileTags.createdAt`, 
`files->FileTags`.`updatedAt` AS `files.FileTags.updatedAt`, 
`files->FileTags`.`FileId` AS `files.FileTags.FileId`, 
`files->FileTags`.`TagId` AS `files.FileTags.TagId` 
FROM `Tags` AS `Tag` 
LEFT OUTER JOIN `FileTags` AS `files->FileTags` 
ON `Tag`.`id` = `files->FileTags`.`TagId` 
LEFT OUTER JOIN `Files` AS `files` 
ON `files`.`id` = `files->FileTags`.`FileId` 
GROUP BY `Tag`.`id` ORDER BY `fileCount` DESC;

尽管有这么多别名，我对加入感到不舒服。

Answer 1

我对类似结构的数据库进行了一些更改。。。这确实给我返回了一些0计数行，但这是我运行它的postgres，而且你显然使用了不同的

SELECT tag.id, 
COUNT(story.id) AS fileCount
FROM tag 
LEFT OUTER JOIN story_tag
ON tag.name = story_tag.tag 
LEFT OUTER JOIN story 
ON story.id = story_tag.story_id 
GROUP BY tag.id ORDER BY fileCount DESC;