在 sequelize 中使用 group by 和 joins [英] 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";

我不要求选择 payment.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: []

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

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

这篇关于在 sequelize 中使用 group by 和 joins的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！