使用分组依据并加入续集 [英] Using group by and joins in sequelize

问题描述

我在PostgreSQL数据库上有两个表，合同和付款。一份合同完成了多次付款。

I have two tables on a PostgreSQL database, contracts and payments. One contract has multiple payments done.

我有以下两种模型：

 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;
};

我想对每个合同的所有付款进行汇总，并使用以下功能：

I would like to sum all the payments made for every contract, and used this 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";

我不要求选择payments.id，因为我必须将其包括在聚合中或按功能分组，如错误中所述：

I do not ask to select payments.id, because I would have to include it in my aggregation or group by functions, as said in the error I have:

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

Possibly unhandled SequelizeDatabaseError: error: column "payments.id" must appear in the GROUP BY clause or be used in an aggregate function

我在这里缺少什么吗？我正在关注此答案，但即使到那里我也不清楚SQL请求如何有效。

Am I missing something here ? I'm following this answer but even there I don't understand how the SQL request can be valid.

推荐答案

此问题已在Sequelize中修复。 3.0.1中，必须使用

This issue has been fixed on Sequelize 3.0.1, the primary key of the included models must be excluded with

attributes: []

，并且汇总必须在主模型上完成（此github问题）。

and the aggregation must be done on the main model (infos in this github issue).

因此，在我的用例中，代码如下

Thus for my use case, the code is the following

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']
})

这篇关于使用分组依据并加入续集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！