Using group by and joins in sequelize

Question

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.

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