I have 2 tables using sequelize as follows

sequelize.define("campaign",{
        id:{
         type:Sequelize.INTEGER,
         autoIncrement:true,
         primaryKey:true,
         allowNull:false
        },
        title:{
            type:Sequelize.STRING
        },
        description:{
            type:Sequelize.STRING
        },
        targetamount:{
            type:Sequelize.INTEGER
        },
        startdate:{
            type:Sequelize.DATE
        },
        enddate:{
            type:Sequelize.DATE
            
        }
    });

sequelize.define("transaction",{
        id:{
         type:Sequelize.INTEGER,
         autoIncrement:true,
         primaryKey:true,
         allowNull:false
        },
        date:{
            type:Sequelize.DATE
        },
        amount:{
            type:Sequelize.INTEGER
        },
        notes:{
            type:Sequelize.STRING
        }
    });

and table transaction associated with campaign as

db.Transactions.belongsTo(db.Campaigns);
db.Campaigns.hasMany(db.Transactions,{as:"campaign"});

Now I am trying to get all the campaigns with sum of amounts collected from the transactions using the below code

    const pageSize = 10000;
    const offset = 0;
    Transactions.findAndCountAll({
        attributes: [[Sequelize.fn('sum', Sequelize.col('amount')), 'total']],
        group:['campaignId'],
        limit:pageSize,
        offset:offset
    }).then(async (data)=>{
        ///
    });

but this is giving the error,

RequestError: Column "transactions.id" is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.

It seems sequelize returning 'id' column by default. how can I solve this?

CodePudding user response：

So the SQL that's being generated under-the-hood is invalid.

Order is being applied because you're asking for limit and offset (usually used for paging) which requires ordering to be deterministic.

To mitigate this you can either:

1. remove the limit and offset
2. provide an explicit ORDER BY that is either in your grouping (campaignId) or the aggregate (total)

Something like this, perhaps?

const pageSize = 10000;
const offset = 0;
Transactions.findAndCountAll({
    attributes: [[Sequelize.fn('sum', Sequelize.col('amount')), 'total']],
    group:['campaignId'],
    limit:pageSize,
    offset:offset,
    order: ['campaignId']
})