Sequelize.JS Postgresql- How to SUM from associated table to compare field in master table

Question

Here, my models and its relations.

// One to Many Relationship between receiptitems and receipts
db.Receipts.hasMany(db.ReceiptItems,{ foreignKey : 'receipt_id'});
db.ReceiptItems.belongsTo(db.Receipts,{ foreignKey : 'receipt_id'});

// One to Many Relationship between ReceiptItems and Payments
// This relation exists due to solve the problem of paying the debts later on !
db.Receipts.hasMany(db.Payments, { foreignKey : 'receipt_id' });
db.Payments.belongsTo(db.Receipts, { foreignKey : 'receipt_id' });

// One to many Relationship between Receipts and Plates
db.Plates.hasMany(db.Receipts, { foreignKey : 'plate_id' });
db.Receipts.belongsTo(db.Plates, { foreignKey : 'plate_id' });

Here, what I want to achieve is that I want to find the receipts that matches with the plate_id and find the receipts where each of its sum of payments are lower then the fee of the receipt.

// db.Op.lt means that "less than"

db.Receipts.findAll({
        where : {
          plate_id : result.plate_id,
          fee : { [ db.Op.lt ] : db.Payments.sum('receivedPayment')}
        },
        include : [db.Receipts.associations.Payments,
          db.Receipts.associations.ReceiptItems,
        ]
      }).then((receiptResult)=>{
        console.log("result"+JSON.stringify(receiptResult));
      }).catch((receiptErr)=>{
        console.log(receiptErr);
      })

Answer 1

For those who may have same concern, here the method for one way of doing this.

db.Receipts.findAll({
    group: ['Receipts.receipt_id', 'ReceiptFees->User.user_id', 'ReceiptPayments->User.user_id'],
    attributes: [
      [db.sequelize.fn('SUM', db.sequelize.col('ReceiptFees.fee')), 'totalFee'],
      [db.sequelize.fn('SUM', db.sequelize.col('ReceiptPayments.receivedPayment')), 'totalPayment']
    ],
    include: [
      {
        model: db.ReceiptFees,
        attributes: [],
        include: [
          { association: db.ReceiptFees.associations.User },
        ]
      },
      {
        model: db.ReceiptPayments,
        attributes: [],
        include: [
          { association: db.ReceiptPayments.associations.User },
        ]
      }
    ],
  }).then(res=> // do your work...)

Feel free to send me a feedback after you try that.