How to query for objects between date ranges

Question

I have documents in mongodb collection that looks like below:

 "listingReservation" : {
    "reservationFromDate" : new Date("14.1.2015 00:00:00"),
    "reservationToDate" : new Date("17.1.2015 00:00:00"),
    "reservationCreatedBy" : "test@test.com",
    "reservationCreatedOn" : "",
    "reservationChangedBy" : "",
    "reservationChangedOn" : "",
    "reservationStatus" : "pending"  //status can be pedning, accepted or rejected  
  }

Now when the admin accepts a reservation, other reservation between the same date ranges and reservations which is encompassing the accepted reservation should be set to rejected status.

Now how can I find if there are already reservations objects between the new reservation dates or if there is already an encompassing reservation for the new reservation dates.

I tried below query but obviously all conditions are not working.

db1.reservationTable.update(
            {
                $and: [{
                    $or: [{

                        $or: [
                            {
                                'listingReservation.reservationFromDate': {
                                    $gte: new Date(req.body.listingReservation.reservationFromDate),
                                    $lt: new Date(req.body.listingReservation.reservationToDate)
                                }
                            },
                            {
                                'listingReservation.reservationToDate': {
                                    $gte: new Date(req.body.listingReservation.reservationFromDate),
                                    $lt: new Date(req.body.listingReservation.reservationToDate)
                                }
                            }
                        ],
                        $and: [
                            {'listingReservation.reservationFromDate': {$lte: new Date(req.body.listingReservation.reservationFromDate)}},
                            {'listingReservation.reservationToDate': {$gte: new Date(req.body.listingReservation.reservationToDate)}}
                        ]
                    }]
                },
                    {'listingBasics.listingId': req.body.listingBasics.listingId},
                    {_id: {$ne: mongojs.ObjectId(req.body._id)}},
                    {'listingBasics.listingOwner': req.user.username}
                ]
            },
            {
                $set: {'listingReservation.reservationStatus': 'rejected'}
            })

score 2 · Answer 1 · edited Jun 20 '20 at 09:12

The logical question to ask is

Are there any reservations where:

the start date is beforereq.body.listingReservation.reservationFromDateand the end date is afterreq.body.listingReservation.reservationFromDate

or

the start date is beforereq.body.listingReservation.reservationToDateand the end date is afterreq.body.listingReservation.reservationToDate

or

the start date is afterreq.body.listingReservation.reservationFromDateand the end date is beforereq.body.listingReservation.reservationToDate?

Here's a possible query based on yours:

db1.reservationTable.update(
  {
    $or: [
      {
        'listingReservation.reservationFromDate': {
          $lte: new Date(req.body.listingReservation.reservationFromDate)
        }, 'listingReservation.reservationToDate': {
          $gte: new Date(req.body.listingReservation.reservationFromDate)
        }
      }, {
        'listingReservation.reservationFromDate': {
          $lte: new Date(req.body.listingReservation.reservationToDate)
        }, 'listingReservation.reservationToDate': {
          $gte: new Date(req.body.listingReservation.reservationToDate)
        }
      }, {
        'listingReservation.reservationFromDate': {
          $gte: new Date(req.body.listingReservation.reservationFromDate)
        }, 'listingReservation.reservationToDate': {
          $lte: new Date(req.body.listingReservation.reservationToDate)
        }
      }
    ],
    'listingBasics.listingId': req.body.listingBasics.listingId,
    _id: {$ne: mongojs.ObjectId(req.body._id)},
    'listingBasics.listingOwner': req.user.username
  }, {
    $set: {'listingReservation.reservationStatus': 'rejected'}
  })

thanks for answer, but one condition here is still not met and I am banging my head to understand how this can be incorporated. Below is scenario I tested, my reservations looks like this (start-end)- ( 14 to 17), (18-19), (23-25),& (15-20). Now accepting(14-17) rejects (18-19) & (15-20). Accepting (18-19)rejects (15-20) these two scenarios are correct. But accepting (15-20) only rejects (14-17), I would want even (18-19) to be rejected. Is this because of mongodb? because as I read from your logical question all conditions should be covered. — Chidu Murthy, Jan 11 '15 at 23:16
P.S: I have voted up the answer, but this is still not full answer ;) — Chidu Murthy, Jan 11 '15 at 23:21
Could you provide the actual sample data in the question? A simple `db1.reservationTable.find()` with any sensitive data redacted? — NoOutlet, Jan 12 '15 at 11:35

score 2 · Accepted Answer · answered Jan 12 '15 at 15:35

If I'm understanding the question correctly, you are looking for a query to find if any reservations overlap with a date range, and set the status of those reservations to rejected. The standard query to do this, in terms of your setup, is

db1.reservationTable.update({ 
        "reservationFromDate" : { "$lte" : new Date(req.body.listingReservation.reservationToDate) },            
        "reservationToDate" : { "$gte" : new Date(req.body.listingReservation.reservationFromDate) } 
    }, 
    { "$set" : { "reservationStatus" : "rejected" } }, 
    { "multi" : true }
)

If we call the time periods being tested for overlap T and S, the condition in plain English is that T starts before S ends, and T ends after S starts. If you think about it you can see that this guarantees that T and S overlap.

This does cover all the logical bases. I'm not sure why I couldn't find this simplified query form. Nicely done! — NoOutlet, Jan 12 '15 at 17:23

How to query for objects between date ranges

2 Answers2