我在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',autoIncrement: true
},contract_id: {
type: DataTypes.INTEGER,},payment_amount: DataTypes.INTEGER,{
classMethods: {
associate: function(models) {
payments.belongsTo(models.contracts,{
foreignKey: 'contract_id'
});
}
}
});
return payments;
};
我想把每个合同的所有付款都算出来,并用这个功能：
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";
我不要求选择payment.id,因为我必须将其包含在我的聚合或组中的功能,如我所说的错误所述：
Possibly unhandled SequelizeDatabaseError: error: column “payments.id”
must appear in the GROUP BY clause or be used in an aggregate function
我在这里遗漏了什么吗？我正在关注this answer,但即使在那里我不明白sql请求如何有效.
解决方法
此问题已在Sequelize 3.0.1中修复,包含的模型的主键必须与…一起排除
attributes: []
并且必须在主模型上进行汇总(this github issue中的信息).
因此,对于我的用例,代码如下
models.contracts.findAll({
attributes: ['id',[models.sequelize.fn('sum','total_cost']],attributes: []
}
],group: ['contracts.id']
})
