How to get ordered data by max amount after summation in mysql through Sequelize ORM

Viewed 24

I need to fetch data ordered by max amount. when i try to add max in sequelize i got some error* i don't know how to solve it , i am new in sequelize and node js*

 await Store.findAndCountAll({
          include: [
            { model: Transactions, attributes: ["createdAt"], order: [["createdAt", "DESC"]] },
            {model: Company, attributes: ["id", "company_name"]},
         ],
        attributes: ["id", "store_external_id", "company_id", "store_name", "createdAt", "is_test_store"],
        order: [["createdAt", "DESC"]],
        distinct: true,
        limit,
        offset,
    })
    .then(async (data) => {
         if ( !isEmpty(data.rows) ) {
            let storeIds=[];
            let transactionsDate ;
                data.rows.forEach(element => {
                    storeIds.push(element.id)
                });
                dateFilter.store_id = storeIds;
                const transactionSum = await Transactions.findAll({
                        where: dateFilter,
                            attributes: ['store_id', 
                                [sequelize.fn('sum', sequelize.col('order_amount')), 'totalBalance'],
                                [sequelize.fn('sum', sequelize.col('convenience_charge')), 'fee'],
                                [sequelize.fn('sum', sequelize.col('amount')), 'orderAmount'],
                                [sequelize.fn('sum', sequelize.col('tip')), 'tipAmount'],
                                // sequelize.fn('MAX', sequelize.literal('order_amount'))
                            ],
                        group: ['store_id'],
                        raw:true
                });
                for await (const transaction of data.rows   ){
                    const singleTransaction = transactionSum.find(element => _.toNumber(element.store_id) === _.toNumber(transaction.id));
                    if(singleTransaction ){
                    transaction.setDataValue( 'totalBalance', singleTransaction ? singleTransaction.totalBalance : 0 )
                    transaction.setDataValue( 'fee', singleTransaction ? singleTransaction.fee : 0 )
                    transaction.setDataValue( 'orderAmount', singleTransaction ? singleTransaction.orderAmount : 0 )
                    transaction.setDataValue( 'tipAmount', singleTransaction ? singleTransaction.tipAmount : 0 )
                    }
                }
        }
    })  
  
0 Answers
Related