This is the function I'm using

MyModel.aggregate([
  { $match: query },
  { $sort: { 'createdAt': -1 } },
  { $skip: skip },
  { $limit: 10 }
  ], { allowDiskUse : true });

query is to filter the rows. skip is dynamic value based on pagination (i.e 0, 10, 20 ...). The problem is, the rows for each page in wrong. For instance, I can see a specific row in page 1,2,3,4 at the same time! some rows are missing as well.

Why is it happening?

CodePudding user response：

the dynamic value based on pagination (i.e 0, 10, 20 ...) should be used for limit, then the skip should be (page - 1 * limit)

let say,

const limit = 10;
const page = 1;
const skip = (page - 1) * limit; // basically is 0

MyModel.aggregate([
  { $match: query },
  { $sort: { 'createdAt': -1 } },
  { $skip: skip },
  { $limit: limit }
  ], { allowDiskUse : true });

so your query will skip 0 data and start with the first result up 10th

so if you page query is 2,

const limit = 10;
const page = 2;
const skip = (page - 1) * limit; // basically is 10

MyModel.aggregate([
  { $match: query },
  { $sort: { 'createdAt': -1 } },
  { $skip: skip },
  { $limit: limit }
  ], { allowDiskUse : true });

so your query will skip 10 data and start with the 11th result up 20th

CodePudding user response：

I think the key to this question is the information that you shared in this comment:

createdAt for many rows has the same value. is it a probability for $sort not to work properly?

It's not that the sort doesn't "work properly" necessarily, it's that it doesn't have enough information to sort deterministically each time. The value of 123 doesn't come before or after another value of 123 on its own. As @Noel points out, you need to provide an additional field(s) to your sort.

This is also covered here in the documentation:

MongoDB does not store documents in a collection in a particular order. When sorting on a field which contains duplicate values, documents containing those values may be returned in any order.

If consistent sort order is desired, include at least one field in your sort that contains unique values. The easiest way to guarantee this is to include the _id field in your sort query.

This is because the _id field is unique. If you take that approach it would change your sort to:

{ $sort: { 'createdAt': -1, _id: 1 } },