Pagination with alphabetical sort

I’m trying to implement pagination with alphabetical sort in a Realm application using Application functions, on a collection with more than 100,000 documents.

Unfortunately it seems we can’t use “collation” in Realm application functions, so I can’t sort my records properly. It’s also quite challenging to handle the pagination part because of other limitations like “skip” not being available.

Has anyone figured this out? Is this eventually going to change?

Thanks.

Hi @Benoit_Werner,

Welcome to MongoDB community!

I believe you will be able to use collation and $skip as an aggregation stage, have you encounter issues through .aggregate command?

Collation in queries is available when using a SYSTEM context function at the moment:

Skip is not a suggested way to do paging for large collections.

What I would suggest is doing the following alternative:

  1. Build an index with collation and options your search require.
  2. Use collation queries from your function and specify the order and filter on that field with a $gt and limit to page the data forward. For next batch pass max value of the sorted output and page forward.

Let me know if you have any questions.

Best
Pavel

Hi Pavel,

Thanks for your help, I’ve manage to make pagination work.
But it seems like the collation index I created is not being used.
Because I always get upper cased letters first A-Z then lower cased…
I’ve tried also with strength 1, 2 and 3 in both index collation options and function call, but I always get the same results. What am I missing?

This is my code:

    return collection.aggregate(
      [
        {
          $match: {
            columnKey: "xxx"
          },
        },
        { $sort: { bodyTxt: 1 } },
        { $limit: 100 },
        {
          $project: {
            bodyTxt: 1,
          },
        },
      ],
      { collation: { "locale": "fr", strength: 2 }}
    );

I’ve tested my code in MongoDB Compass to see if the index was applied. Not only is it applied, but I get the sorting that I expected.
So I’m guessing that it’s just within Realm that the collation is not applied. (confirmed by the documentation here: https://docs.mongodb.com/realm/mongodb/crud-and-aggregation-apis/#database-command-availability).
Is this ever going to change, is collation ever going to be supported?
I really love MongoDB Realm and its potential and I wish I could use it for my project.

Hi @Benoit_Werner,

Potentially you can use views created directly on Atlas but it will come with performance panalty.

Let me know if you have any questions.

CC: @Drew_DiPalma

Best
Pavel