I have a mysql table with an enum field storing the state of elements, e.g.:
These states get translated into the user's locale in the application, and since the translations can differ greatly, it is not possible to sort records by state in the Mysql query, since the order of the enum values will not match the order of the translated strings.
For example:
SELECT state FROM records ORDER BY state ASC
Would give the following results for english, german and french:
As Mysql sorts by the enum values, using this order in the application makes it seem like the sorting by state is jumbled.
Of course it is possible to do the sorting by state afterwards in the application using the translated strings, but it would remove a layer of complexity to be able to do this directly in the query - as well as improve application performance.
One solution I found would be to use a CASE statement in the query:
SELECT
CASE state
WHEN 'draft' THEN 'Entwurf'
WHEN 'inactive' THEN 'Inaktiv'
WHEN 'published' THEN 'Freigeschaltet'
END
FROM
records
ORDER BY
state ASC
Are there better/faster ways to sort an enum by custom strings translated in the application?
you can use ORDER BY FIELD(state, opt1, opt2, opt3....)
https://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_field
as its just a CSV list you should be able to use the application to pass the order you want.