SQL 在 sequelize 中使用 group by 和 joins
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/28206680/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Using group by and joins in sequelize
提问by syldor
I have two tables on a PostgreSQL database, contracts and payments. One contract has multiple payments done.
我在 PostgreSQL 数据库上有两个表，合同和付款。一份合同已完成多次付款。
I'm having the two following models:
我有以下两个模型：
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']
})
But it generates the following query:
但它会生成以下查询：
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";
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:
我不要求选择 Payments.id，因为我必须将它包含在我的聚合或按功能分组中，正如我在错误中所说：
Possibly unhandled SequelizeDatabaseError: error: column "payments.id" must appear in the GROUP BY clause or be used in an aggregate function
可能未处理的 SequelizeDatabaseError：错误：列“payments.id”必须出现在 GROUP BY 子句中或用于聚合函数中
Am I missing something here ? I'm following this answerbut even there I don't understand how the SQL request can be valid.
我在这里错过了什么吗？我正在关注这个答案，但即使在那里我也不明白 SQL 请求如何有效。
回答by syldor
This issue has been fixed on Sequelize 3.0.1, the primary key of the included models must be excluded with
此问题已在 Sequelize 3.0.1 上修复，包含模型的主键必须使用
attributes: []
and the aggregation must be done on the main model (infos in this github issue).
并且必须在主模型上完成聚合（此 github 问题中的信息）。
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']
})
回答by Rahul Bhati
Can you write your function as
你能把你的函数写成
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']
})
回答by Calvintwr
Try
尝试
group: ['contracts.id', 'payments.id']
回答by swifty
Is the issue that you might want to be selecting from paymentsand joining contractsrather than the other way around?
您可能希望从中选择payments并加入contracts而不是相反的问题吗？
