Sequelize - 在不同条件下两次计算相同的关联 [英] Sequelize - Counting the same association twice under different conditions
问题描述
我正在尝试根据不同的参数获取关联表中的两个记录数:
I'm trying to fetch two counts of records in an associated table based on different parameters:
// 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 查询中的两个表实例分配相同的名称:
The issue is that when including the same associated table multiple times, Sequelize assigns the same name to both instances of the table in the SQL query:
LEFT OUTER JOIN Invoices
AS Invoices
ON Customer
.id
= Invoices
.CustomerId
AND Invoices
.isArchived
= 0
LEFT OUTER JOIN Invoices
AS Invoices
ON Customer
.id
= Invoices
.CustomerId
AND Invoices
.isArchived
= 0 AND Invoices
.status
= 'UNPAID'>
LEFT OUTER JOIN
Invoices
ASInvoices
ONCustomer
.id
=Invoices
.CustomerId
ANDInvoices
.isArchived
= 0
LEFT OUTER JOINInvoices
ASInvoices
ONCustomer
.id
=Invoices
.CustomerId
ANDInvoices
.isArchived
= 0 ANDInvoices
.status
= 'UNPAID'
有什么方法可以为查询中的连接表指定一个不同的名称?例如:
Is there any way to specify a different name to use for the joined table in the query? For example:
LEFT OUTER JOIN Invoices
AS Invoices
ON Customer
.id
= Invoices
.CustomerId
AND Invoices
.isArchived
= 0
LEFT OUTER JOIN Invoices
AS UnpaidInvoices
ON Customer
.id
= Invoices
.CustomerId
AND Invoices
.isArchived
= 0 AND Invoices
.status
= '未付款'
LEFT OUTER JOIN
Invoices
ASInvoices
ONCustomer
.id
=Invoices
.CustomerId
ANDInvoices
.isArchived
= 0
LEFT OUTER JOINInvoices
ASUnpaidInvoices
ONCustomer
.id
=Invoices
.CustomerId
ANDInvoices
.isArchived
= 0 ANDInvoices
.status
= 'UNPAID'
推荐答案
我通常只加入一次,然后使用带有 SUM
的 CASE
语句而不是使用 COUNT
- 行都连接在一起,这就是为什么您对两者都相同的原因.
I typically just join once and then use a CASE
statement with SUM
instead of using COUNT
- the rows are all joined is why you get the same for both.
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' ],
],
// ...
},
这篇关于Sequelize - 在不同条件下两次计算相同的关联的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!