javascript - 使用 Sequelize 获取关系计数为零的实例
问题描述
下面是通过 Sequelize 计算模型关系的语法
const files = await db.File.findAll({
attributes: {
include: [
[Sequelize.fn('COUNT', 'Tags.id'), 'tagCount']
]
},
include: [
{
model: db.Tag,
as: 'tags',
attributes: [],
duplicate: false
}
],
group: 'File.id',
order: [
[Sequelize.literal('`tagCount`'), 'DESC']
]
})
我需要什么才能使此代码仅返回与它们相关联的零标签的文件?
编辑
根据@Soham 提供的答案,它确实达到了预期的结果,但无法分页。生成的查询是:
SELECT `File`.`id`,
`File`.`originalId`,
`File`.`signature`,
`File`.`referrer_url`,
`File`.`preview_url`,
`File`.`preview_extension`,
`File`.`original_url`,
`File`.`original_extension`,
`File`.`provider`,
`File`.`previewedAt`,
`File`.`viewedAt`,
`File`.`blacklistedAt`,
`File`.`queuedAt`,
`File`.`startedAt`,
`File`.`downloadedAt`,
`File`.`createdAt`,
`File`.`updatedAt`,
COUNT(`tags`.`id`) AS `tagCount`,
`tags->FileTags`.`createdAt` AS `tags.FileTags.createdAt`,
`tags->FileTags`.`updatedAt` AS `tags.FileTags.updatedAt`,
`tags->FileTags`.`fileId` AS `tags.FileTags.fileId`,
`tags->FileTags`.`tagId` AS `tags.FileTags.tagId`
FROM `Files` AS `File`
LEFT OUTER JOIN `FileTags` AS `tags->FileTags`
ON `File`.`id` = `tags->FileTags`.`fileId`
LEFT OUTER JOIN `Tags` AS `tags`
ON `tags`.`id` = `tags->FileTags`.`tagId`
GROUP BY `File`.`id`
HAVING `tagCount` = 0
ORDER BY `tagCount` DESC;
当我按照以下方式向查询添加偏移量和限制时
return super.findAll({
offset: 0,
limit: 24,
attributes: {
include: [
[Sequelize.literal('COUNT(`tags`.`id`)'), 'tagCount']
]
},
include: [
{
model: db.Tag,
as: 'tags',
attributes: [],
duplicate: false
}
],
group: 'File.id',
order: [
[Sequelize.literal('`tagCount`'), 'DESC']
],
having: { tagCount: 0 }
})
这会输出以下内容
SELECT `File`.*,
`tags->FileTags`.`createdAt` AS `tags.FileTags.createdAt`,
`tags->FileTags`.`updatedAt` AS `tags.FileTags.updatedAt`,
`tags->FileTags`.`fileId` AS `tags.FileTags.fileId`,
`tags->FileTags`.`tagId` AS `tags.FileTags.tagId`
FROM (SELECT `File`.`id`,
`File`.`originalId`,
`File`.`signature`,
`File`.`referrer_url`,
`File`.`preview_url`,
`File`.`preview_extension`,
`File`.`original_url`,
`File`.`original_extension`,
`File`.`provider`,
`File`.`previewedAt`,
`File`.`viewedAt`,
`File`.`blacklistedAt`,
`File`.`queuedAt`,
`File`.`startedAt`,
`File`.`downloadedAt`,
`File`.`createdAt`,
`File`.`updatedAt`,
COUNT(`tags`.`id`) AS `tagCount`
FROM `Files` AS `File`
GROUP BY `File`.`id`
HAVING `tagCount` = 0
ORDER BY `tagCount` DESC LIMIT 0,
24) AS `File`
LEFT OUTER JOIN `FileTags` AS `tags->FileTags`
ON `File`.`id` = `tags->FileTags`.`fileId`
LEFT OUTER JOIN `Tags` AS `tags`
ON `tags`.`id` = `tags->FileTags`.`tagId`
ORDER BY `tagCount` DESC;
解决方案
要过滤标签计数为零的记录,您需要使用 having 子句,如下所示 -
const files = await db.File.findAll({
attributes: {
include: [
[Sequelize.fn('COUNT', 'Tags.id'), 'tagCount']
]
},
include: [
{
model: db.Tag,
as: 'tags',
attributes: [],
duplicate: false
}
],
group: 'File.id',
order: [
[Sequelize.literal('`tagCount`'), 'DESC']
],
having: { tagCount : 0 },
subQuery: false
})
推荐阅读
- php - 具有隐藏属性的功能测试身份验证
- powershell - PowerShell Start-Job 未运行脚本块
- javascript - 我如何单击并且可以在 Javascript 上显示自己的类
- unity3d - 2d 文本聚焦在相机上,但当相机围绕对象旋转时旋转 180
- javascript - I18n-js - 有没有办法检查翻译是否存在?
- r - 从匹配正则表达式的第一列开始删除列范围
- css - 如何更改下拉菜单中的图标?
- spring-boot - Spring boot JPA - 使用Controller中的额外列更新ManyToMany关系的中间表的最佳解决方案是什么?
- debugging - 如何使用 JTAG 链接调试两个或多个微控制器?
- linux - Linux 中带有“module_pci_driver”的 pci 设备驱动程序和带有“__init()”但没有“module_pci_driver()”的 pci 驱动程序之间的区别