Room reservation query from SQL to MongoDB

Question

I am working on conference room reservation application based by MongoDB. I have business entities:

class Room {
    private String id;
    // ...
}

class RoomReservation {
   private String id;
   private String room_id;
   private String user_id;
   private Date start;
   private Date end;
   // ...
}

To find out any available conference rooms in a given period (start, end), I could have a query something like the following if I use a relationship database.

select * from room as r where r.id not in (
  select room_id from room_reservation as rr
  where (rr.start <= start and rr.end >= start)
  union
  select room_id from room_reservation as rr
  where (rr.start <= end and rr.end >= end)
  union 
  select room_id from room_reservation as rr
  where (rr.start >= start and rr.end <= end)
  union
  select room_id from room_reservation as rr
  where (rr.start <= start and rr.end >= end)
)

I can have a similar query statement for MongoDB. The approach won't be very effective however. Unlike an individual field returned in a SQL statement, a whole document data, RoomReservation needs to be returned as a result of a MongoDB query.

Is the aggregation more suitable for this problem? If so, how to use aggregation operations to solve this problem?

Answer 1

Unless I'm missing something here the union queries were even overkill here. In essence this is asking that the items contained within each condition set are not part of the results obtained from the collection.

This is what a $nor operator in MongoDB does by making sure than none of the conditions specified are met in selection of the documents.

In essence the structure is like this:

db.collection.find({
    "$nor": [
        { "start": { "$lte": start }, "end": { "$gte": start } },
        { "start": { "$lte": end },   "end": { "$gte": end } },
        { "start": { "$gte": start }, "end": { "$lte": end } },
        { "start": { "$lte": start }, "end": { "$gte": end } }
    ]
})

Not sure of the helper method construction without firing up an IDE, but the operators here should be what you are generally looking for the query structure to come out as.

--

Using the helper methods from spring data:

    Query query = new Query().addCriteria(
        new Criteria().norOperator(
                Criteria.where("start").lte(start).and("end").gte(end),
                Criteria.where("start").lte(end).and("end").gte(end),
                Criteria.where("start").gte(start).and("end").lte(end),
                Criteria.where("start").lte(start).and("end").gte(end)
        )
    );