Mysql sql how can I sort a table based on Locale?

Question

My application is using a search function, sometimes the search may return a result set with thousands of items, therefore I am using lazy loading and only retrieving the primary keys.

The problem is that my application is localized, and I need to sort this primary keys alphabetically using another column in the table that has a name that is later localized in code (using the typical key-value pairs .properties file), this sorting varies depending on the Locale.

I know one solution would be to have another table, that basically contains exactly what my .properties file that I use for localization has. But this seems like an extreme solution, is there any other way I can achieve this?. Thank you in advance.

Answer 1

No, not really.

You are basically saying "retrieve primary keys from database, use .properties file to simulate another database, sort in application", correct? The database doesn't really come into the equation here after step 1.

The solution you are proposing (import .properties file into a table, sort there) actually sounds quite reasonable from the data you gave. How often do these .properties files change? Could you automate the process? (e.g. re-import the changed files)

Also, the database is good at sorting and filtering large amounts of data; doing this in a different layer will be, in 90% of cases, less efficient - reinventing the database-wheel outside the database is rather painful in the long run.