'Show all Dates data between two dates; if no row exists for particular date then show zero in all columns using sequelize
folks I m stuck in logical or syntax problem, I have tried so many thing from stack. But I didn't get the exact solution on my Problem.
Problem
I have table abc with following columns
╔════╦═════════╦═════════════════════╦═══════╗
║ id ║ success ║ transactionTime ║ value ║
╠════╬═════════╬═════════════════════╬═══════╣
║ 1 ║ 1 ║ 2018/09/12 10:50:00 ║ 10 ║
╠════╬═════════╬═════════════════════╬═══════╣
║ 2 ║ 0 ║ 2018/09/13 10:58:00 ║ 12 ║
╠════╬═════════╬═════════════════════╬═══════╣
║ 3 ║ 1 ║ 2018/09/13 10:55:00 ║ 34 ║
╚════╩═════════╩═════════════════════╩═══════╝
I wanted Show All the data between two dates; if no row exists for particular date then show zero in all columns using sequelize ;
I understand with the SQL but not getting how to do with Sequilze ORM
I have Tried this
startDate = moment().subtract(7, 'days').format('YYYY-MM-DD 00:00:00');
endDate = moment().format('YYYY-MM-DD 23:59:59');
const Record = await db.abc.count({
where: {
transactionTime: {
$between: [
startDate,
endDate,
],
},
},
attributes: [[sequelize.fn('DATE_FORMAT', sequelize.col('transactionTime'), '%Y-%m-%d'), 'day']],
group: [sequelize.fn('DAY', sequelize.col('transactionTime'))],
});
I Am getting the following Output
day Count
2018/09/12 1
2018/09/13 2
Expected Output :
day Count
2018/09/08 0
2018/09/09 0
2018/09/10 0
2018/09/11 0
2018/09/12 1
2018/09/13 2
Help me out, I am new in sequelize.
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
