Using group by and joins in sequelize
Solution 1
This issue has been fixed on Sequelize 3.0.1, the primary key of the included models must be excluded with
attributes: []
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']
})
Solution 2
Try
group: ['contracts.id', 'payments.id']
Solution 3
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']
})
Solution 4
Is the issue that you might want to be selecting from payments
and joining contracts
rather than the other way around?
Comments
-
syldor almost 4 years
I have two tables on a PostgreSQL database, contracts and payments. One contract has multiple payments done.
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:
Possibly unhandled SequelizeDatabaseError: error: column "payments.id" must appear in the GROUP BY clause or be used in an aggregate function
Am I missing something here ? I'm following this answer but even there I don't understand how the SQL request can be valid.