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 )
}
}
}
})