sequelize.js - 如何查找关联模型中列的总和大于 x 的所有行

Question

我是 sequelize.js 的新手。
我正在使用 mysql 和 sequelize.js 来存储数据的小项目。

我有 Member 和 Loan 模型。

 var Member = sequelize.define('members', {
    rollNumber: {
        type: Sequelize.INTEGER,
        unique:true,
        primaryKey:true,
        unsigned:true
    },
    firstName: {
        type: Sequelize.STRING(20)
    },
    middleName: {
        type: Sequelize.STRING(20)
    },
    lastName: {
        type: Sequelize.STRING(20)
    },

    idNumber: {
        type: Sequelize.STRING(20),
        unique:true,
    },
    mobileNumber: {
        type: Sequelize.STRING(20)
    }
});




 var Loan = sequelize.define('loans',{
    memberRollNumber:{
        type: Sequelize.INTEGER,
        references: { model: Member, key: "rollNumber" },
        allowNull: false
    },
    disbursedAmount:{
      type: Sequelize.FLOAT
    },
    interestRate:{
        type: Sequelize.FLOAT
    },

    initialBalance:{
        type: Sequelize.FLOAT
    },
    duration:{
        type: Sequelize.INTEGER
    },
    disbursementDate:{
        type: Sequelize.DATEONLY
    },
    dueDate:{
        type: Sequelize.DATEONLY
    }
});

Loan.belongsTo(Member); Member.hasMany(Loan);
现在，我想获取 Loan.initialBalance 总和大于“1000”或任何其他数字的所有成员。

function getLoans(callback){
    Member.findAll({
        include: [ Loan ] ,
        where:{}
    }).then(function (members) {

        callback(members)
    })
}

我花了几个小时试图弄清楚 where:{} 子句中的内容。请就可能的解决方案提出建议。

Answer 1

为此，您需要通过 group_by 和 having 来完成，这里是如何使用 sequelize 做到这一点:

Member.findAll({
    include : {
        model : Loan
        attributes : [],
        required : true 
    },
    group : ['members.id','loans.initial_balance'],
    having : {
        $and : [
            // condition to check sum of initialBalance is greater than 1000
            sequelize.where(sequelize.fn('sum', sequelize.col('initial_balance')), { $gt : 1000 }) ,
        ]
    }
})

Note : Please change column name as per your DB.