CustomerAccountService

CustomerAccountService

我有两张 table
CustomerAccountCustomerAccountService
它们的关系如下:

CustomerAccount.hasMany(CustomerAccountService, {
    foreignKey: 'account_uuid'
});

CustomerAccountService.belongsTo(CustomerAccount, {
    as: 'account',
    foreignKey: 'account_uuid'
});

CustomerAccountService 有两个名为 invoice_amount 和 core_amount 的列。

我想获取所有 CustomerAccount 的列表,在该列表中,我想显示所有发票金额的总和及其子 CustomerAccountService 记录的所有核心金额。

这是我正在尝试的查询:
CustomerAccount.findAll({
    attributes: [
        'uuid',
        'account_name',
        'account_number',
        'emergency_pay',
        [Sequelize.fn('SUM', Sequelize.col('CustomerAccountService.invoice_amount')), 'totalInvoiceAmount'],
        [Sequelize.fn('SUM', Sequelize.col('CustomerAccountService.core_amount')), 'totalCoreAmount']
    ],
    include: [
        {
            model: CustomerAccountService,
            attributes: []
        }
    ],
    group: ['CustomerAccount.uuid']
}).then(...);

但是它抛出一个错误说



如何从关联表中获取两列的总和?

CustomerAccountService 的模型定义是:
return sequelize.define(
        'customer_accounts_services',
        {
            uuid: {
                type: type.STRING,
                primaryKey: true,
                autoIncrement: false
            },
            account_uuid: type.STRING,
            account_number: type.STRING,
            account_type: type.STRING,
            payment_type: type.STRING,
            service_type: type.STRING,
            date: type.DATEONLY,
            description: type.TEXT,
            invoice_amount: type.DECIMAL(10,2),
            core_amount: type.DECIMAL(10,2),
            paid: type.BOOLEAN
        },
        {
            timestamps: false,
            underscored: true,
            tableName: 'customer_accounts_services'
        }
    );

最佳答案

您的模型定义将表名设置为 customer_accounts_services 但您将 CustomerAccountService 的模型名称传递给 Sequelize.col() ,该名称不存在,因此您收到有关缺少列的错误。

更新您的查询以在 Sequelize.col() 中使用正确的表名。

[Sequelize.fn('SUM', Sequelize.col('customer_accounts_services.invoice_amount')), 'totalInvoiceAmount'],
[Sequelize.fn('SUM', Sequelize.col('customer_accounts_services.core_amount')), 'totalCoreAmount']

关于mysql - Sequelize 从关联表中获取列的总和,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/56704613/

10-16 14:50
查看更多