How to do pagination using range queries in MongoDB?

Question

I'm probably missing something since I'm still learning the ins and outs of MongoDB, but I need help with paging a collection.

I have a collection that has a list of names.

Bottom Round of Beef
Chicken Breast 6oz
Chicken Breast 8oz
Chicken Breast 8oz
Chicken Breast 8oz
Chicken Breast Random
Chicken Legs
Chicken Tenderloin
Chicken Thighs
Kosher Salt

I created a compound index on "ProductName,_id".

I run this query:

db.ProductGuideItem.find( { ProductName: { $gt: "Chicken Breast 8oz" } } ).sort({ProductName:1,_id:1}).limit(3);  

Notice there are 3 "Chicken Breast 8oz" items.

If I run that query I get...
Chicken Breast Random
Chicken Legs
Chicken Tenderloin

If I was paging and started from the top. The query would have missed the other 2 "Chicken Breast 8oz".

So if each page can only have 3 items and I want to see page 2 then I should see..
Chicken Breast 8oz
Chicken Breast 8oz
Chicken Breast Random.

But I'm not. It's going to the last Chicken Breast 8oz and starting from there instead.

Is there a way around this?
Also how would I do this if the list was sorted the opposite way?

Answer 1

Since the collection I was paging had duplicate values I had to create a compound index on ProductName and id.

Create Compound Index

db.ProductGuideItem.ensureIndex({ ProductName:1, _id:1}); 

This solved my problem.
Reference: https://groups.google.com/d/msg/mongodb-user/3EZZIRJzW_A/oYH79npKZHkJ

Assuming you have these values:

{a:1, b:1} {a:2, b:1} {a:2, b:2} {a:2, b:3} {a:3, b:1} 

So you do this for the range based pagination (page size of 2):

1st Page

find().sort({a:1, b:1}).limit(2) {a:1, b:1} {a:2, b:1} 

2nd Page

find().min({a:2, b:1}).sort({a:1, b:1}).skip(1).limit(2)  {a:2, b:2} {a:2, b:3} 

3rd Page

find().min({a:2, b:3}).sort({a:1, b:1}).skip(1).limit(2) {a:3, b:1} 

Here are the docs for $min/max: http://www.mongodb.org/display/DOCS/min+and+max+Query+Specifiers

If you don't have duplicate values in your collection, you don't need to use min & max or create a compound index. You can just use $lt & $gt.