Implementing Pagination in Mongodb

Implementing pagination in mongodb

The concept you are talking about can be called "forward paging". A good reason for that is unlike using .skip() and .limit() modifiers this cannot be used to "go back" to a previous page or indeed "skip" to a specific page. At least not with a great deal of effort to store "seen" or "discovered" pages, so if that type of "links to page" paging is what you want, then you are best off sticking with the .skip() and .limit() approach, despite the performance drawbacks.

If it is a viable option to you to only "move forward", then here is the basic concept:

db.junk.find().limit(3)

{ "_id" : ObjectId("54c03f0c2f63310180151877"), "a" : 1, "b" : 1 }
{ "_id" : ObjectId("54c03f0c2f63310180151878"), "a" : 4, "b" : 4 }
{ "_id" : ObjectId("54c03f0c2f63310180151879"), "a" : 10, "b" : 10 }

Of course that's your first page with a limit of 3 items. Consider that now with code iterating the cursor:

var lastSeen = null;
var cursor = db.junk.find().limit(3);

while (cursor.hasNext()) {
   var doc = cursor.next();
   printjson(doc);
   if (!cursor.hasNext())
     lastSeen = doc._id;
}

So that iterates the cursor and does something, and when it is true that the last item in the cursor is reached you store the lastSeen value to the present _id:

ObjectId("54c03f0c2f63310180151879")

In your subsequent iterations you just feed that _id value which you keep ( in session or whatever ) to the query:

var cursor = db.junk.find({ "_id": { "$gt": lastSeen } }).limit(3);

while (cursor.hasNext()) {
   var doc = cursor.next();
   printjson(doc);
   if (!cursor.hasNext())
     lastSeen = doc._id;
}

{ "_id" : ObjectId("54c03f0c2f6331018015187a"), "a" : 1, "b" : 1 }
{ "_id" : ObjectId("54c03f0c2f6331018015187b"), "a" : 6, "b" : 6 }
{ "_id" : ObjectId("54c03f0c2f6331018015187c"), "a" : 7, "b" : 7 }

And the process repeats over and over until no more results can be obtained.

That's the basic process for a natural order such as _id. For something else it gets a bit more complex. Consider the following:

{ "_id": 4, "rank": 3 }
{ "_id": 8, "rank": 3 }
{ "_id": 1, "rank": 3 }    
{ "_id": 3, "rank": 2 }

To split that into two pages sorted by rank then what you essentially need to know is what you have "already seen" and exclude those results. So looking at a first page:

var lastSeen = null;
var seenIds = [];
var cursor = db.junk.find().sort({ "rank": -1 }).limit(2);

while (cursor.hasNext()) {
   var doc = cursor.next();
   printjson(doc);
   if ( lastSeen != null && doc.rank != lastSeen )
       seenIds = [];
   seenIds.push(doc._id);
   if (!cursor.hasNext() || lastSeen == null)
     lastSeen = doc.rank;
}

{ "_id": 4, "rank": 3 }
{ "_id": 8, "rank": 3 }

On the next iteration you want to be less or equal to the lastSeen "rank" score, but also excluding those already seen documents. You do this with the $nin operator:

var cursor = db.junk.find(
    { "_id": { "$nin": seenIds }, "rank": "$lte": lastSeen }
).sort({ "rank": -1 }).limit(2);

while (cursor.hasNext()) {
   var doc = cursor.next();
   printjson(doc);
   if ( lastSeen != null && doc.rank != lastSeen )
       seenIds = [];
   seenIds.push(doc._id);
   if (!cursor.hasNext() || lastSeen == null)
     lastSeen = doc.rank;
}

{ "_id": 1, "rank": 3 }    
{ "_id": 3, "rank": 2 }

How many "seenIds" you actually hold on to depends on how "granular" your results are where that value is likely to change. In this case you can check if the current "rank" score is not equal to the lastSeen value and discard the present seenIds content so it does not grow to much.

That's the basic concepts of "forward paging" for you to practice and learn.

How to implement pagination for fastapi with mongo db(Motor)

There is no right or wrong answer to such a question. A lot depends on the technology stack that you use, as well as the context that you have, considering also the future directions of both the software you wrote as well as the software you use (mongo).

Answering your questions:

1. It depends on the load you have to manage and the dev stack you use. Usually it is done at database level, since retrieving the first 110 and dropping the first 100 is quite dumb and resource consuming (the database will do it for you).

2. To me is seems pretty simple on how to do it via fastapi: just add to your get function the parameters limit: int = 10 and skip: int = 0 and use them in the filtering function of your database. Fastapi will check the data types for you, while you could check that limit is not negative or above, say, 100.

3. It says that there is no silver bullet and that since skip function of mongo does not perform well. Thus he believes that the second option is better, just for performances. If you have billions and billions of documents (e.g. amazon), well, it may be the case to use something different, though by the time your website has grown that much, I guess you'll have the money to pay an entire team of experts to sort things out and possibly develop your own database.

TL;DR

Concluding, the limit and skip approach is the most common one. It is usually done at the database level, in order to reduce the amount of work of the application and bandwidth.

Mongo is not very efficient in skipping and limiting results. If your database has, say a million of documents, then I don't think you'll even notice. You could even use a relational database for such a workload. You can always benchmark the options you have and choose the most appropriate one.

I don't know much about mongo, but I know that generally, indexes can help limiting and skipping records (docs in this case), but I'm not sure if it's the case for mongo as well.

How to paginate with Mongoose in Node.js?

After taking a closer look at the Mongoose API with the information provided by Rodolphe, I figured out this solution:

MyModel.find(query, fields, { skip: 10, limit: 5 }, function(err, results) { ... });

Implementing mongodb pagination along with match query?

Using your filter object you can do something like this:

Use these steps to ensure a good pagination:

1. Sort by any value (to ensure not get random positions)
2. Skip by the number of pages
3. Limit by the number of elements into page

So, the query will be something like (not tested but you can see the idea):

const elementsPerPage = filter.size
const nSkip = elementsPerPage * filter.page
const sort = {[filter.sortOn]:filter.order}
YourModel.find({/*yourquery*/})
  .limit(elementsPerPage)
  .skip(nSkip)
  .sort(sort)

Also, you can use filter values into your query, something like:

YourModel.find({
  role: filter.role, 
  status:filter.status,
  name:{ $regex: filter.search}
})

This query is like this example.

Also, is not defined what calues do you want to use, the condition etc, so, with this, you can use if/else to add or not values into query.

For example:

var query = {}
if(filter.search){
  query.name = {$regex: filter.search}
}

So all together can be:

const elementsPerPage = filter.size
const nSkip = elementsPerPage * filter.page
const sort = {[filter.sortOn]:filter.order}

var query = {}
if(filter.search){
  query.name = {$regex: filter.search}
}
if(filter.role){
  query.role = filter.role
}
if(filter.status){
  query.status = filter.status
}

YourModel.find(query)
  .limit(elementsPerPage)
  .skip(nSkip)
  .sort(sort)

Note that this has not been tested, but as I've said before you can see the idea with this example.

implement pagination REST API using mongoDB and express.js

I solved this problem on my own,
the code is:

router.route('/category/outer/all/:page').get((req, res) => {
    const upcycle = upcycleModel.aggregate([
        {"$match": {"soldout": false, "koLCategory": "keyword"}},
        {"$project":
            {
                "_id": 0,
                "soldout": 0,
            }
        }
    ]);
    const organic = organicModel.aggregate([
        {"$match": {"soldout": false, "koLCategory": "keyword"}},
        {"$project":
            {
                "_id": 0,
                "soldout": 0,
            }
        }
    ]);
    const vegan = veganModel.aggregate([
        {"$match": {"soldout": false, "koLCategory": "keyword"}},
        {"$project":
            {
                "_id": 0,
                "soldout": 0,
            }
        }
    ]);
    const mixed = mixedModel.aggregate([
        {"$match": {"soldout": false, "koLCategory": "keyword"}},
        {"$project":
            {
                "_id": 0,
                "soldout": 0,
            }
        }
    ]);
    /* this part */
    const page = req.params.page;
    const limit = 30;
    const startIndex = (page - 1) * limit;
    const endIndex = page * limit;
    Promise.all([
        upcycle,
        organic,
        vegan,
        mixed
    ]).then(data => {
        result = data[0].concat(data[1]).concat(data[2]).concat(data[3]).slice(startIndex, endIndex);
        res.json({
            product: {
                totalCnt: result.length,
                items: result
            }
        })
    }).catch(err => res.status(400).json('Error: ' + err));
});

If you have any more efficient solution, please leave comments.

Paginate Mongoose / MongoDB query: sort by two fields starting from a point

What you are describing is called pagination.

How to implement this pattern is already described in depth by many others (like this). leaving this here for others

The reason your query does not return any documents on the next page, however, is probably simple because the tiebreaker is implemented incorrectly (I am assuming you are using the _id as tiebreaker).

It should look similar to this:

// page 1
collection
  .find({})
  .sort({ rating: -1, _id: -1 })
  .limit(limit)
  .toArray();

// page n
collection
  .find({
    $and: [
      { ...any actual query here },
      // this is the pagination query
      {
        $or: [
          { rating: { $lt: lastRating } },
          {
            rating: lastRating,
            _id: { $lt: ObjectId(lastID) } 
        ]
    ]
  })
  .sort({ rating: -1, _id: -1 })
  .limit(limit)
  .toArray();

---

Related Topics