我有两张 table CustomerAccount
和 CustomerAccountService
它们的关系如下:
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/