sql – 在后续中使用group by和join

我有两个以下型号：

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请求如何有效.

解决方法

attributes: []

并且必须在主模型上进行汇总(this github issue中的信息).

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

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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!