Need to sort using natural sort

Hello everyone, I am new to appwrite and would like to know if it is possible to sort using Natural sort. In other words, sorting alphabetically would yield "Test1", "Test10", "Test2". What I want is "Test1", "Test2", "Test10". I normally use MongoDB for the database and solve this using the collation setting (on the db level). How is it possible to do that in appwrite?

Thank you for your precious time. I appreciate it!

You would need to change the data so that it sorts alphabetically.

Out of curiosity, how exactly would you handle this in mongo?

in mongo its very easy. You just specify the collation and make sure to set numericOrdering to true

db.c.find(
   { }, { _id: 0 }
).sort(
  { n: 1 }
).collation( {
  locale: 'en_US',
  numericOrdering: true
} )

The example is from the docs: https://www.mongodb.com/docs/manual/reference/collation/

@Drake What if I cannot change the data? I have user input that I cannot control. And it has to be sorted out naturally. Not being able to do that in appwrite, will not allow me to use the platform. Is there anyway we can solve that?

Interesting...and this numeric ordering works with other non-numeric text in the string too? Like "text1" and "text10"?

I found some references that it's not possible 🧐

https://stackoverflow.com/questions/68965441/sort-on-number-that-is-embedded-in-a-string-field-in-mongodb-collection.

If it was just the number in a string, it would be possible, but the extra text is the problem

One approach could be to have an extra attribute that is populated client side or via an Appwrite function that can be used for sorting

@Drake What I mentioned was not a "suggestion" it is the actual technique I use in my projects, and yes it does sort alphanumeric content like "text 1", "text 10".

a fast tutorial if you're interested: https://www.tutorialspoint.com/how-to-do-alphanumeric-sort-in-mongodb

also in postgres if you're interested. https://stackoverflow.com/questions/9173558/postgresql-order-by-issue-natural-sort

Feel free to submit a feature request with these as reference

To be honest I don't know how to do that. Can you help?

https://github.com/appwrite/appwrite/issues and click New to create a new issue

thank you

Thanks for creating https://github.com/appwrite/appwrite/issues/7203

As part of the pitch, would you please include why this is important for you to have?

Understanding your use-case/why is important for us