caseInsensitive find not working

I’m trying to sort my users by the name (case insensitive) but can’t get it working.
Here is a simple cloud code:

var teamUserQuery = new Parse.Query('TeamMember')
teamUserQuery.ascending('fullName')
const teamRet = await teamUserQuery.find({ sessionToken: sessionToken, caseInsensitive:true  })

I’m still getting capital letters and small letters sorted separately. It’s not working.
When I try to explain query I’m getting an error (it works without caseInsensitive:true):
teamRet is not iterable

I’ve created index on TeamMember :
db.TeamMember.createIndex( { fullName: 1 }, { collation: { locale: ‘en_US’, strength: 2 } } );

Still wrong order, but when I try to query the db with mongodb compass the order works well when using {locale:‘en_US’} or {locale:‘en’}
What am I doing wrong?

Do you have other index in place for fullName field? Also, it would be good if you could manage to run with explain. Could you please share the code that you are using to run with explain true and caseInsensitive true?

yes there is a fullText search index on fullName.
I’m trying to use parse explain() function before calling find (just to check)
Here is a code

    var teamUserQuery = new Parse.Query('TeamMember')
    teamUserQuery.select('userId', 'status', 'fullName')
    teamUserQuery.equalTo('teamId', tools.pointerTeam(teamId))

    teamUserQuery.doesNotExist('deletedAt')
    teamUserQuery.include('userId')
    teamUserQuery.ascending('fullName')
    teamUserQuery.explain()
    const teamRet = await teamUserQuery.find({ sessionToken, caseInsensitive:true  }) ```
1 Like

What is the line throwing the error? Reading your error message (teamRet is not iterable), it looks it is something you are doing with the teamRet var later. Maybe try to console.log teamRet right after find() function?

Maybe you are right, I will recheck it, but still, the main problem is that it’s not sorting records as it is supposed to. When I try to query the database with MongoDB compass it’s ok, but trying a query with the Dashboard console it’s not ordered properly. Maybe I could somehow check which query is generated by Parse?

That’s why the explain would be good to have. So we can check the query and the index being used. I guess it is using your full text search index instead of the one with collation.

Looks like it’s using different index:

              "inputStage":{
                 "stage":"IXSCAN",
                 "keyPattern":{
                    "_p_teamId":1,
                    "email":1
                 },

How to make sure that ordering even if not using right index is done right, should I change somehow collation of the Collection to en_US ? Is there a way to set default collation or maybe the problem is with something else?

You can specify which index should database use with 'hint`

query.hint('index_name');

This should work.

Great hint, thank you I didn’t realize that it’s possible, unfortunately even if it shows that it’s using my index (in explain plan) - it’s still in wrong order (capital letters first). The same is in Parse Dashboard.

I just looked into the source code and it seems that RestQuery.js
RestQuery.prototype.runFind = function (options = {}) {
is not getting in options value caseInsensitive,
if I comment out checking if (caseInsensitive) {

    if (caseInsensitive) {
      findOperation = findOperation.collation(MongoCollection.caseInsensitiveCollation());
    }

it works well, but it’s ignoring caseInsensitive:true from the find call.

Please help me, maybe I’m using the not proper version of some package?
I can’t find in the source code a call to options.hasOwnProperty('caseInsensitive') so how it could be passed to query options? Hard to quess where it dissapears suddenly
In the worst case I will just manually edit MongoCollection.js
I just realized that it’s not copied from ParseQuery.js options to findOptions, so how it is supposed to work? I have no idea.

FYI, I just fixed the issue by dumping and restoring table with changed collation in metadata (had to change the collation for table and main index only {"locale":"en_US","strength":{"$numberInt":"2"}).
Now it works just fine, I still think that caseInsensitive is ignored and it’s a bug.