sql - Sequelize - 在不同条件下两次计算相同的关联
问题描述
我正在尝试根据不同的参数在关联表中获取两个记录计数:
// Models
var Customer = sequelize.define('Customer', {
name: DataTypes.STRING,
});
var Invoice = sequelize.define('Invoice', {
invoiceRef: DataTypes.STRING,
status: {
type: DataTypes.ENUM,
values: ['UNPAID', 'PAID'],
defaultValue: 'UNPAID'
},
isArchived: {
type: DataTypes.BOOLEAN,
defaultValue: false
},
});
Invoice.associate = function(models) {
Invoice.belongsTo(models.Customer);
}
Customer.associate = function (models) {
Customer.hasMany(models.Invoice);
}
// Query
Customer.findAll({
attributes: {
include: [
[models.Sequelize.fn("COUNT", models.Sequelize.fn("DISTINCT", models.Sequelize.col("Invoices.id"))), "totalInvoices"],
[models.Sequelize.fn("COUNT", models.Sequelize.fn("DISTINCT", models.Sequelize.col("UnpaidInvoices.id"))), "unpaidInvoices"]
]
},
include: [
{
model: models.Invoice,
where: { isArchived: false },
attributes: [],
required: false
},
{
model: models.Invoice,
where: { isArchived: false, status: 'UNPAID' },
attributes: [],
required: false
},
],
group: ['Customer.id']
})
问题在于,当多次包含同一个关联表时,Sequelize 会为 SQL 查询中表的两个实例分配相同的名称:
左外连接为
Invoices
ON 。= 。和。= 0 左外连接为ON 。= 。和。= 0 和。='未付'Invoices
Customer
id
Invoices
CustomerId
Invoices
isArchived
Invoices
Invoices
Customer
id
Invoices
CustomerId
Invoices
isArchived
Invoices
status
有没有办法为查询中的连接表指定不同的名称?例如:
左外连接为
Invoices
ON 。= 。和。= 0 左外连接为ON。= 。和。= 0 和。='未付'Invoices
Customer
id
Invoices
CustomerId
Invoices
isArchived
Invoices
UnpaidInvoices
Customer
id
Invoices
CustomerId
Invoices
isArchived
Invoices
status
解决方案
我通常只加入一次,然后使用with而不是 using的CASE
语句- 所有行都加入了这就是为什么你对两者都得到相同的原因。SUM
COUNT
attributes: {
include: [
// count paid using case/sum via `status` != 'UNPAID'
[ sequelize.fn('sum', sequelize.literal("CASE WHEN (`Invoices`.`status` != 'UNPAID' THEN 1 ELSE 0 END")), 'paid_count' ],
// count unpaid using case/sum via `status` = 'UNPAID'
[ sequelize.fn('sum', sequelize.literal("CASE WHEN (`Invoices`.`status` = 'UNPAID' THEN 1 ELSE 0 END")), 'unpaid_count' ],
],
// ...
},
推荐阅读
- python - python - 使用 groupby 填充熊猫
- vba - 为什么 DLookup 不返回事务期间更新的值?
- tensorflow - 各自的 CNN(DCGAN) 模型的代码和内核值是多少?TF 凯拉斯
- c++ - OpenGL 纹理缩小伪影
- java - Google App Engine 标准应用程序是否可以访问本地计算机的文件?
- unity3d - (Unity) MonoBehaviour 脚本的枚举替代方案
- c++ - 如何以构造函数在 C++ 中初始化所有类对象的方式从 C 中的结构初始化所有对象?
- xml - 完整且简洁的 XPath 3.1 参考
- swift - 具有类别/扩展名的核心数据未声明类型
- python - 链接列表:打印列表时 Sublime 文本崩溃