12

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?

Simon
  • 19,658
  • 27
  • 149
  • 217
  • 1
    Well, I can answer one part. ``BasicQuery`` accepts a JSON string and your string uses single quotes or no quotes at all for attribute names, which is not valid JSON format. – Patrick Roberts Jun 16 '15 at 17:02
  • Hello thanks for your response. I tried BasicQuery query1 = new BasicQuery("{'_id': {'$lt':'ObjectId(\"55732dccf58c555b6d3f1c5a\")'}}).limit(5).sort({'_id':-1}") but it didn't end up working. So eventually, I just used the Query object instead of BasicQuery. – Simon Jun 16 '15 at 18:14
  • 1
    That's still not valid JSON. Here's an example of valid JSON using BasicQuery: ``BasicQuery query1 = new BasicQuery('{"_id": {"$lt": "ObjectId(\"55732dccf58c555b6d3f1c5a\")"}}').limit(5).sort('{"_id": -1}');`` This is valid JSON I can say for sure, but I cannot say for sure whether this will work or not. – Patrick Roberts Jun 16 '15 at 18:43
  • Thanks - i tried this but had to escape on every " or ' and then it still didn't work as i was getting a 500 server error. I think my answer below is probably the best solution for now. Thanks for your responses. – Simon Jun 16 '15 at 19:14

2 Answers2

18

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));
Community
  • 1
  • 1
Simon
  • 19,658
  • 27
  • 149
  • 217
3

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.

logixplayer
  • 939
  • 2
  • 13
  • 23