I need to get each user's transactions every three days. I want to know users that don't have up to a certain amount(200) within the three days period, then get the sum of all the transactions for each user. I want to exclude Sunday since transactions are always low on Sundays.
I want to make sure this is done right from the DB because the transactions from each user can run into thousands even millions.
I am using dayjs to manipulate the time but I am not getting it right
I have been able to get the three previous date and the current date. The previous date will be the startDate and the current date will be endDate.
But I need to remove if Sunday is in the range and use that to query the database.
This is what I have done what I am not close to fixing it.
How can I query the transaction table by dateCreated and exclude sundays?
schema sample
export const TransactionSchema = new mongoose.Schema({
description: {
type: String
},
ref: {
type: String,
required: true
},
userID: {
type: mongoose.SchemaTypes.ObjectId,
ref: 'User'
},
amount: {
type: Number,
required: true,
},
commission: {
type: Number,
default: 0
},
responseDescription: {
type: String
},
authCode: {
type: Number
},
isDeleted: {
type: Boolean,
default: false
},
dateCreated: {
type: Date,
default: Date.now
},
dateUpdated: {
type: Date,
required: false
},
openingBalance: {
type: Number
},
closingBalance: {
type: Number
},
})
method
async getUserRequiredTargetTrans() {
let now = dayjs();//endDate
let fromThreeDays = now.subtract('2', 'day')
let sunday = now.day(0)
let withOutSunday = now.diff(fromThreeDays);//startDate
const response = await this.transactionModel.find({ isDeleted: false, dateCreated: { $gte: withOutSunday, $lt: now } })
To exclude sundays from date range, you can use $where operator like this:
async getUserRequiredTargetTrans() {
let from = dayjs();//endDate
let fromThreeDays = now.subtract('2', 'day')
const response = await this.transactionModel.find({ isDeleted: false, dateCreated: { $gte: fromThreeDays, $lt: now },
// exculde sunday
$where: `function() { return this.dateCreated.getDay() !== 0;}`
} )
You pass a dayjs, don't know whether you can use it directly. Perhaps you have to use
find({ isDeleted: false, dateCreated: { $gte: withOutSunday.toDate(), $lt: now.toDate() } })