1

I am using spring data with mongodb to create an application.

I have this object:

public class Room {
    private String name;
    private List<Date> occupied;
}

I want using mongodbTemplate preferably to get the list of room that are not occupied for a date range.

So for example if i have a start date 10/10/2014 and end date 15/10/2014 I want to get the list of rooms that do not have in the list occupied the dates 10,11,12,13,14,15 for October 2014.

Does anyone have any idea on this?

Update:

I have found a way to do this by using this query:

query.addCriteria(Criteria.where("occupiedDates")
   .ne(from).andOperator(
       Criteria.where("occupiedDates").ne(to))
);

the problem is that I can not dynamically add the andOperator.

I would prefer inside the criteria to add a list of dates if possible.

An example document is (only one record exists in mongo this one) :

Room(bedcount=1, bedtype1=1, bedtype2=0, bedtype3=0, bedtype4=0,
filetype=null, noofrooms=0, occupancy=0, photo=null, rateid=1,
roomname=null, roomno=888, status=null,
roomTypeID=26060747427845848211948325568, occupiedDates=[Sun Aug 10
00:00:00 EEST 2014, Mon Aug 11 00:00:00 EEST 2014, Tue Aug 12 00:00:00
EEST 2014, Wed Aug 13 00:00:00 EEST 2014], attributes={})

And this is the code of how the wyeru is built:

SimpleDateFormat dateFormat = new SimpleDateFormat("dd-M-yyyy hh:mm:ss");
Date to = null;
Date from = null;
try {
    to = dateFormat.parse("12-08-2014 00:00:00");
    from = dateFormat.parse("10-08-2014 00:00:00");
} catch (ParseException e) {
    // TODO Auto-generated catch block
    e.printStackTrace();
}
DBObject c1 = new BasicDBObject("occupied", null);
DBObject c2 = BasicDBObjectBuilder.start()
    .push("occupied").push("$not")
    .push("$elemMatch").add("$gte", from).add("$lte", to).get();

Criteria c = Criteria.where("$or").is(Arrays.asList(c1, c2));
Query query = new Query().addCriteria(c);
List<Room> rooms = mongoTemplate.find(query, Room.class);

This query is sent to mongodb

{ "$or" : [
  { "occupied" :  null } ,
  { "occupied" :
    { "$not" :
      { "$elemMatch" :
        { "$gte" : { "$date" : "2014-08-09T21:00:00.000Z"} ,
          "$lte" : { "$date" : "2014-08-11T21:00:00.000Z"}
        }
      }
    } 
  }
]}

from this we understand that the query should return nothing. but it returns me 1 row.

Improve this question
2
  • It's not a direct answer, but why don't you read the docs first: docs.spring.io/spring-data/mongodb/docs/1.3.x/reference/html/… It's really helpful and will solve your problem. Commented Oct 3, 2014 at 9:43
  • 1
    of course i have read the docs and i can not find a sollution, otherwise i would not ask :) Commented Oct 3, 2014 at 9:50

2 Answers 2

Reset to default
1

As the requirements I understood eventually, you want to fetch all documents in which none of elements of occupied falls into the specified date range.

Complete on mongo shell: 

db.b.find({
    $or : [{
        occupied : null
    }, {
        occupied : {
            $not : {
                $elemMatch : {
                    $gte : start,
                    $lte : end
                }
            }
        }
    }]
}).pretty();

Then translate to Java code as below: 

// Because "Criteria" has a bug when invoking its method "elemMatch", 
// so I build the criteria by the driver directly, almost.  

DBObject c1 = new BasicDBObject("occupied", null);
DBObject c2 = BasicDBObjectBuilder.start().push("occupied").push("$not").
                        push("$elemMatch").add("$gte", start).add("$lte", end).get();
Criteria c = where("$or").is(Arrays.asList(c1, c2));
Query query = new Query().addCriteria(c);
List<Room> rooms = mongoTemplate.find(query, Room.class);
Improve this answer
Sign up to request clarification or add additional context in comments.

8 Comments

Hi again, this query does not work unfortunately, it always returns me all of the rooms. The changes i have done is I have cast c2 to BasicDBObject becuase the .get return DBObject. Where you have start and end variable I have put java.util.Date variables. :(
@giannisapi, I'm sorry C2 should be DBObject type. I've corrected them in the answer. After your modification, does it work for you?
still the same, it returns all the values. also another change is Criteria c = where should be Criteria c = Criteria.where. am i correct?
when i get the list of the objects, the date elements are in the format: Sun Aug 10 00:00:00 EEST 2014 and when I execute the query the format sent is : 2014-08-11T21:00:00.000Z maybe this is why the query fails? Note that I create both dates in the same format and in the same way.
@giannisapi, could you post some documents that it should be not listed as you expected. And your start and end value. By the way, I import the method "where" so I can write in that style. Criteria.where is the original format and it's right.
|
0

Analysis routing

According to your question, suppose there are some resources as below:

  • occupied = [d1, d2, d3, d4, d5]; // for easier representation, suppose elements are in ascending order
  • range = [start, end];

So, you want to return every document if its data satisfy one of the following criteria after sorting in ascending order:

  • start, end, d1, d2, d3, d4, d5 // equivalent to: min(occupied) > end
  • d1, d2, d3, d4, d5, start, end // equivalent to: max(occupied) < start

If elements are stored in order in occupied, it is easy to fetch the minimum and maximum value. But you don't mention it, so I suppose essentially they are not in order.

Unfortunately, there is no operator to get minimum or maximum value from an array in standard query. But according to the feature of array field in comparison of standard query, matching will return true if at least one element or itself satisfying the criteria; return false only all fail the criteria.
It's lucky to find that min(occupied) > end is equivalent to NOT (at least one element <= end), which is the key point to achieve by following method.

Fulfill on mongo shell: 

db.b.find({
    $or: [{
        occupied: {
            $not: {
                $lte: end
            }
        }
    }, {
        occupied: {
            $not: {
                $gte: start
            }
        }
    }]
}). pretty();

Then translate to Java code like this: 

Criteria c = new Criteria().orOperator(where("occupied").not().lte(end),
        where("occupied").not().gte(start));
Query query = new Query().addCriteria(c);
List<Room> rooms = mongoTemplate.find(query, Room.class);
Improve this answer

5 Comments

hi, I think you have misunderstood my question. or i misunderstood your answer :) . Did you read this: So for example if i have a start date 10/10/2014 and end date 15/10/2014 I want to get the list of rooms that do not have in the list occupied the dates 10,11,12,13,14,15 for October 2014. The occupied list stores dates not ranges, so it may have 10/10/1985,11/10/1985 but it will not have 10/10/1985 - 15/10/1985, instead for abooking of 5 days it will have 5 records such as : 10/10/1985,11/10/1985,12/10/1985,13/10/1985,14/10/1985. do i explain it clearly? thanks for the help
@giannisapi, I understood your intent. For example, if there is a document whose occupied values are : 10/10/1985,11/10/1985,12/10/1985,13/10/1985,14/10/1985, then, it should be selected if s=02/10/1985, e=09/10/1985, and it should not be selected if s=02/10/1985, e=11/10/1985 because of intersection of 10/10/1985 and 11/10/1985. Is it right? :)
you are absolutely correct :) i need to look more carefully and understand your answer better. thank you :)
hi again, i think now i understand your answer, unfortunately i do not think though that this is correct. because occupied may have values such as 10/10/1985,11/10/1985,12/10/1985,15/10/1985,16/10/1985. then if a query comes with starting date 13/10/1985 and end date 14/10/1985 then your sollution will return nothing (saying that the room is occupied for those dates). am i correct?
@giannisapi, You are right. Now I understand that what you exactly want to do is to fetch all documents in which none of elements of occupied falls into the specified date range. Correct? Your original explanation is so cool that I failed to caught it. :) I add another answer for you.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.