按顺序查询日期范围(createdAt)

Question

我想在一系列开始和结束日期之间进行顺序查询，但似乎不起作用。我试过使用between，但似乎也不起作用。在sequelize上实现这一点的正确方法是什么？下面的查询有什么问题？非常感谢。

#我的代码

if (query && query.startDate && query.endDate) {
query = {
createdAt: {
[Op.between]: [query.startDate, query.endDate],
},
};
}

#也尝试过

if (query && query.startDate && query.endDate) {
query = {
[Op.and]: [
{ createdAt: { [Op.gte]: query.startDate } },
{ createdAt: { [Op.lte]: query.endDate } },
],
};
}

#控制台查询

query {
'$skip': '0',
'$limit': '25',
startDate: '2020-07-19',
endDate: '2020-07-20'
}

Answer 1

下面是一些演示代码，例如

// building query string to 
buildQuery(query) {
var whereClause = {                      

};
// pms filter is necessery 
if(query && query.pms_id) { 
whereClause['pms_id'] = query.pms_id;
}
// checkin date filter
if(query && query.start_date) {            
const arrivalFrom = moment(query.start_date).subtract(2, 'months').format('YYYY-MM-DD');
whereClause['checkin_date'] >  arrivalFrom;
} else {
const arrivalFrom = moment().subtract(2, 'months').format('YYYY-MM-DD');
whereClause['checkin_date'] >  arrivalFrom;
}
// checkout date filter
if(query && query.end_date) {            
const arrivalFrom = moment(query.end_date).add(2, 'months').format('YYYY-MM-DD');
whereClause['checkout_date'] <  arrivalFrom;
} else {
const arrivalFrom = moment().add(2, 'months').format('YYYY-MM-DD');
whereClause['checkout_date'] <  arrivalFrom;
}
return whereClause;
}
// query for getting bookings related to user
getBookings(user_id,query="") {
return bookingModel.findAndCountAll({
where: this.buildQuery(query), 
include:[
{ model:pmsModel, 
where: { user_id:  user_id},
required:false
},
{
model: propertyModel,
required:false
},
{
model: roomModel,
required:false
}
],           
});
}