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 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'

有什么方法可以为查询中的连接表指定一个不同的名称?例如:

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 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 = '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屋！