使用分组和连接

我在PostgreSQL数据库上有两个表,合同和付款。 一份合同有多个付款。

我有以下两个模型:

module.exports = function(sequelize, DataTypes) { var contracts = sequelize.define('contracts', { id: { type: DataTypes.INTEGER, autoIncrement: true } }, { createdAt: false, updatedAt: false, classMethods: { associate: function(models) { contracts.hasMany(models.payments, { foreignKey: 'contract_id' }); } } }); return contracts; }; module.exports = function(sequelize, DataTypes) { var payments = sequelize.define('payments', { id: { type: DataTypes.INTEGER, autoIncrement: true }, contract_id: { type: DataTypes.INTEGER, }, payment_amount: DataTypes.INTEGER, }, { classMethods: { associate: function(models) { payments.belongsTo(models.contracts, { foreignKey: 'contract_id' }); } } }); return payments; }; 

我想总结为每个合同所做的所有付款,并使用了这个function:

 models.contracts.findAll({ attributes: [ 'id' ], include: [ { model: models.payments, attributes: [[models.sequelize.fn('sum', models.sequelize.col('payments.payment_amount')), 'total_cost']] } ], group: ['contracts.id'] }) 

但它会生成以下查询:

 SELECT "contracts"."id", "payments"."id" AS "payments.id", sum("payments"."payment_amount") AS "payments.total_cost" FROM "contracts" AS "contracts" LEFT OUTER JOIN "payments" AS "payments" ON "contracts"."id" = "payments"."contract_id" GROUP BY "contracts"."id"; 

我不要求selectpayments.id,因为我将不得不通过函数将其包含在我的聚合或组中,就像我在错误中所说的那样:

可能未处理SequelizeDatabaseError:错误:列“payments.id”必须出现在GROUP BY子句中或用于聚合函数

我在这里错过了什么? 我正在按照这个答案,但即使在那里,我不明白如何SQL请求可以是有效的。

       

网上收集的解决方案 "使用分组和连接"

这个问题已经在Sequelize 3.0.1上修正了,所包含模型的主键必须被排除在外

 attributes: [] 

并且聚合必须在主模型上完成(在这个github问题中的信息 )。

因此对于我的用例,代码如下

 models.contracts.findAll({ attributes: ['id', [models.sequelize.fn('sum', models.sequelize.col('payments.payment_amount')), 'total_cost']], include: [ { model: models.payments, attributes: [] } ], group: ['contracts.id'] }) 

您可能希望从payments和joincontractsselect问题,而不是其他方式?

你能写你的函数吗?

 models.contracts.findAll({ attributes: [ 'models.contracts.id' ], include: [ { model: models.payments, attributes: [[models.sequelize.fn('sum', models.sequelize.col('payments.payment_amount')), 'total_cost']] } ], group: ['contracts.id'] }) 

尝试

 group: ['contracts.id', 'payments.id']