I'm not sure if this should be a feature request or not, since I'm pretty sure there is no efficient way of achieving this natively today...
I am using pagination, but have a requirement to apply sorting. The challenge is that when sorting the output from a data query, the sort is case sensitive and cannot be changed - meaning that capital letter A-Z comes before a-z in the output. This is a confusing experience for consumers, as it really makes no sense in many apps.
If I use the sort function (applied to the JSON output from the query), I have numerous options to sort the data using different methods... BUT the sort only applies to that "window" of results (e.g. 10 records). From a user perspective, this makes no sense - as it means just the 10 results on that single page may end up sorted a-z, but not the remaining 1000+ records in the database - so you won't see the "final" results from the dataset appearing in the results.
So... why not do both? I apply a sort to the query, and again to the output via function. No dice since I effectively end up with a case sensitive sort due to the way the data query does the sort.
The only solution seems to be to retrieve a full dataset of pre-sorted data, then applying the sort to the object, and implementing custom pagination over the top to "window" through that object. This, as you can imagine, is slow and resource-intensive. Another option might be to iterate through the items in a separate function, and add an additional field in there for the order, then do the sort on that simple numeric field - but all of these options are horrible (and may not even be achievable/practical!).
Perhaps this can only be achieved through direct database query? I'd rather avoid this for maintainability reasons, but this sort issue is quite a problem in my use case.
Has anybody solved this problem of a case insensitive sort?