CODEWITHSUNDEEP
mongodbpagination
Donny V.
Donny V.

Reputation: 23546

How to do pagination using range queries in MongoDB?

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?

Upvotes: 33

Views: 82926

Answers (2)

Donny V.
Donny V.

Reputation: 23546

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.

Update: 2024 After revisiting this and making sure nothing new has popped up. I found the article which talks about getting the total count after every page query and doing it all in 1 request. This also takes into consideration if the total document count changes between getting the count and running the query. It uses the aggregate pipeline instead. https://codebeyondlimits.com/articles/pagination-in-mongodb-the-only-right-way-to-implement-it-and-avoid-common-mistakes

exports.getArticles = async (req, res) => {
  let { page, pageSize } = req.query;

  try {
    // If "page" and "pageSize" are not sent we will default them to 1 and 50.
    page = parseInt(page, 10) || 1;
    pageSize = parseInt(pageSize, 10) || 50;

    const articles = await Articles.aggregate([
      {
        $facet: {
          metadata: [{ $count: 'totalCount' }],
          data: [{ $skip: (page - 1) * pageSize }, { $limit: pageSize }],
        },
      },
    ]);

    return res.status(200).json({
      success: true,
      articles: {
        metadata: { totalCount: articles[0].metadata[0].totalCount, page, pageSize },
        data: articles[0].data,
      },
    });
  } catch (error) {
    return res.status(500).json({ success: false });
  }
};

Produces an output like this

{
  metadata: {
    totalCount: 100,
    page: 1,
    pageSize: 50,
  },
  data: [
    {
      _id: 1,
      title: 'Article 1',
    },
    {
      _id: 2,
      title: 'Article 2',
    },
    ...
  ],
};

Upvotes: 39

Akash Amar
Akash Amar

Reputation: 441

The below code will return 10 docs form 1st page and similarly for rest of the pages.

const perPage = 10 //10docs in single page
const page = 1 //1st page
db.collection.find({}).skip(perPage * page).limit(perPage)

Upvotes: 7

Related Questions