Sort not working when field doesn't exist. (MongoDB)

[Atlinx]

When I sort for a field that doesn't exist on all items, the sort glitches out -- specifically, it results in duplicate elements during pagination. I think when an item doesn't have a field being sorted, that item should be placed last.

[edobrb]

Are you using the in-memory driver? (mock)

[Atlinx]

I've tested it with a real mongodb database and a mock.

[Atlinx]

Weird, everything seemed to sort itself out. Maybe it was an issue with my frontend that led to this. Might be the same reason why #287 disappeared as well for me.

[edobrb]

Thank you for the feedback!

[Atlinx]

@edobrb On second thought, this issue resurfaced again. I realized that it was only fixed because I was working with a database that had the field set. When new entries came in without the field, this bug appeared again.

I'm going to do some investigating on my side to see if it's a frontend issue.

EDIT: See reply below

[edobrb]

In practice, the proposed solution is to add an order by id in order to avoid duplicates. However, this will have an impact on performance even on queries for which this operation would not be required. Furthermore, the problem is also present when using the MongoDB driver. I'm not sure Typetta has to fix this. Take a look at https://stackoverflow.com/questions/56609607/mongodb-strange-behaviour-of-sort-with-pagination-leading-to-duplicates he suggests adding an index to the field you are ordering on.
Let me know what you think

[edobrb]

The fix would be this:

typetta/src/dal/drivers/no-sql/mongodb/dao.mongodb.ts

Lines 63 to 65 in 6fa41fd

	/*if (sort.length > 0 && !sort.map((v) => v[0]).includes(this.dbIdField)) { // see #288
	sort.push([this.dbIdField, 1])
	}*/

[Atlinx]

Would I be able to leverage using the index out the box with Typetta, or would I have to write a manual MongoDB-specific query?

[Atlinx]

Ah that did the trick! Thanks! ❤️

[edobrb]

At the moment Typetta does not support automatic index creation. Btw you could use the execQuery:

await entityManager.execQuery(async (dbs, entities) => {
    await entities.user?.createIndex(/* ... */)
})

or you could use something like https://www.npmjs.com/package/migrate-mongo for seeding initial data and creating indexes only once.