Search code examples
javascriptmongodbmongoosequery-builderor-operator

Mongoose Query Builder .or not returning expected results

Context
I'm building a query where a user can enter a range of dates to search for 'Player' documents. The query should return any players whose start and end dates overlap with the queried dates. I'm doing this by using an OR condition where either the player's end date is after the query's start date or the player's start date is before the query's end date. Both dates in the query are optional, allowing the user to specify only an end date or a start date, or neither to return all relevant documents.

Problem
The query works when only one of the two query dates are specified (see screenshots below). However, it returns all documents if both dates are specified (functionally ignoring both query parameters).

Code 

let query = Player.find({'casino': user.casino});
let queryArray = [];
if(req.body.startDate) {
        queryArray.push({playerEndDtm: {$gt: moment(req.body.startDate).toDate()}});
}
if(req.body.endDate) {
    queryArray.push({playerStartDtm: {$lt: moment(req.body.endDate).endOf('day').toDate()}});
}
if(queryArray.length > 0) {
    query.or(queryArray);
}

Screenshots
When both query dates are specified: enter image description here
Results:
enter image description here

When only 1 query date is specified: enter image description here
Results:
enter image description here

Can anyone help me determine what I'm doing wrong? Thanks!

Solution 
  • if(req.body.endDate) {
    queryArray.push({playerStartDtm: {$lt: 
    moment(req.body.endDate).endOf('day').toDate()}});
}

    In the above query you are justing checking less then the end date. You need to check range between dates:

     ({ playerStartDtm : { $gt :  expression, $lt : expression}})