CODEWITHSUNDEEP
mongodbmongoosemongodb-querytypegoose
James Fu
James Fu

Reputation: 452

Cursor based pagination search with sorting in MongoDB and include the null field

I try to build up the query to perform the cursor based search and sort some field. But I cannot obtain the correct results for some data with null field if someone sort by this field.

The interface of my API should be

// client can specify the size he wants to get and after is the ID of the last object he gets before
// response contains [totlaCount], [endCursor], [hasNextPage] information
searchUsers(groupId: ID!, size: Int = 10, after: String): Response

If I have the data in the DB like:

User A ("_id": "1", "name": "A")
User B ("_id": "2")
User C ("_id": "3", "name": "C")
User D ("_id": "4")
User E ("_id": "5", "name": "E")
User F ("_id": "6", "name": "F")

I need to sort them by "name", and return the results as following:

First Query (size = 3)
User A ("_id": "1", "name": "A")
User C ("_id": "3", "name": "C")
User E ("_id": "5", "name": "E")

Second Query (size = 3, after = "5")
User A ("_id": "6", "name": "F")
User B ("_id": "2")
User D ("_id": "4")

Here is my query:

let startFrom = {
  $match: {},
};
if (hasLastUser) {
  startFrom = {
    $match: {
      $or: [
        {
          name:
            sortOrder === SortOrder.ASCENDING
              ? { $gt: lastUser.name }
              : { $lt: lastUser.name },
        },
        {
          name: lastUser.name,
          _id: { $gt: after },
        },
      ],
    },
  };
}

const query = await this.userModel.aggregate([
  // Stage 1: Filter
  {
    $match: {
      $and: [
        {
          groups: {
            $elemMatch: {
              group: { $in: ids },
            },
          },
        },
        {
          $or: [
            { email: { $regex: regKeyword } },
            { name: { $regex: regKeyword } },
            { phone: { $regex: regKeyword } },
          ],
        },
      ],
    },
  },

  // Stage 2: Sorting
  {
    $sort: {
      name: sortOrder === SortOrder.ASCENDING ? 1 : -1, // 1 for ascending, -1 for descending,
      _id: 1,
    },
  },
  // Stage 3: Skip previous data
  startFrom,
  // Stage 4: Limit the size
  {
    $limit: size,
  },
]);

BUT!! I found the user which does not contain 'name' field not found in my query:

First Query (size = 3)
User A ("_id": "1", "name": "A")
User C ("_id": "3", "name": "C")
User E ("_id": "5", "name": "E")

Second Query (size = 3, after = "5")
User A ("_id": "6", "name": "F")

How can I solve this query?

Upvotes: 1

Views: 1352

Answers (1)

I.Blair
I.Blair

Reputation: 581

The $match stage is filtering out users without names. In the $or a document will only be returned if it the name matches the regEx, so documents without names will be filtered out.

Playground

Upvotes: 0

Related Questions