How to do pagination using range queries in MongoD

问题:

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?

回答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.

回答2:

You just need to call skip on the cursor

see MongoDB documentation on cursor skip "This approach may be useful in implementing “paged” results".

This example is taken from Mongodb's example

function printStudents(pageNumber, nPerPage) {
   print("Page: " + pageNumber);
   db.students.find().skip((pageNumber-1)*nPerPage).limit(nPerPage).forEach( function(student) { print(student.name + "<p>"); } );
}