MongoDB Pagination with two values

user_2579739 0
Viewed 135

Let's say my MongoDB records look like this:

  1. {category: A , sub_category : 1}
  2. {category: A , sub_category : 2}
  3. {category: A , sub_category : 3}
  4. {category: B , sub_category : 1}
  5. {category: B , sub_category : 2}
  6. {category: B , sub_category : 3}
  7. {category: C , sub_category : 1}
  8. {category: C , sub_category : 2}
  9. {category: C , sub_category : 3}

I am trying to achieve pagination with these values. So, I created a compound index with both of these fields (both in ascending order).

Let's say my page limit is 2.

So my first query looks like this -> db.record.find().sort({category: 1, sub_category: 1}); -> And the first two records are returned.
My second query looks like this -> db.record.find({category : {$gte: A}, sub_category: {$gt : 2}}).sort({category: 1, sub_category: 1}).

I was expecting record 3 and 4 to return. But I got record 3 and record 6.

I understood that my way of querying is not right. I couldn't find another solution. Please help me with this issue.

P.S. I am not using skip and limit technique, because I am following bucket pattern.

mongoose mongodb pagination
2 Answers

When querying in Mongo if the query can be satisfied by an index then Mongo will traverse the index trees (B-tree's) and will not perform collection scan.

So the expected documents order is basically just a "perceived order". the documents "index order" is different and heavily relies on what fields were indexed, document insertion order and more.

So what can you do? you can force your perceived order by using sort:

db.record.find({category : {$gte: A}, sub_category: {$gt : 2}}).sort({category: 1, sub_category: 1});
user_107703700

I followed the suggestions from Tom Slabbaert and Adnan Ahmed Ansari, and the solution worked.

I used this query:

db.record.find({ $or: [ { category : 'A' , sub_category: {$gt : 2}}, { category : {$gt: 'A'} } ] })
user_25797390
Related