Mongotemplate - Query ObjectId according to greater than (gt) or less than (lt) operator

17.9k views Asked by At

When I type this into my consol, it works:

db.posts.find({"_id": {$lt:ObjectId("55732dccf58c555b6d3f1c5a")}}).limit(5).sort({"_id":-1})

When I use mongotemplate, it doesn't work and returns a blank array:

  @RequestMapping(value="/next", method=RequestMethod.GET)
  public List getNextPost(@RequestParam String next) {
      Query query = new Query();
      query.addCriteria(Criteria.where("_id").lt("55732dccf58c555b6d3f1c5a"));
      List<Posts> posts = template.find(query, Posts.class);
      return posts;

  }

I tried it with this query instead and it works but only returns the specific entry related to the id:

query.addCriteria(Criteria.where("_id").is("55732dccf58c555b6d3f1c5a"));

I also tried with Basic Query and did this and it also returns a blank array:

BasicQuery query1 = new BasicQuery("{'_id': {$lt:'ObjectId(\"55732dccf58c555b6d3f1c5a\")'}}).limit(5).sort({'_id':-1}");

I'm stumped. How do I return all the docs below a certain Mongo ObjectID in a database?

2

There are 2 answers

0
Simon On BEST ANSWER

So after searching for an hour, I have found the solution - i had to look at this post which is not in java but in node.js.

Querying a MongoDB based on Mongo ID in a node.js app

Thankfully, the language is close to java so I saw that you cannot query by just inserting the objectID into the lt operator. You will have to create an objectID object and insert that into the operator.

      ObjectId objID = new ObjectId("55732dccf58c555b6d3f1c5a");
      query.addCriteria(Criteria.where("_id").lt(objID));
0
logixplayer On

This post was a life saver. Spent hours searching for this. I wanted to add that the answer above does the "lt" comparison but doesn't show the sorting part.

What I discovered was when you do a "lt" or "gt" comparison in mongodb, it is not guaranteed that you will get the immediate next ASC or DESC ObjectId. In fact, I noticed that when I got to the end of my collection and did an "lt", it reset and gave me the very first document in my collection (instead of the one just before it). For that, I have to add to the above solution which I found here: http://www.mkyong.com/mongodb/spring-data-mongodb-query-document/

Query nextStoryQuery = new Query(); //1
previousStoryQuery.addCriteria(Criteria.where("_id").lt(objID)).limit(1); //2
previousStoryQuery.with(new Sort(Sort.Direction.DESC, "_id")); //3

Note, on line 2, I was just looking for 1 result so I added the limit function. Line 3: By adding the Sort functionality, it guarantees the immediate ASC or DESC ObjectId value based on the current ObjectId value.