Ok, rewrote the query myself using CTE (which is unfortunatelly not supported by QueryBuilder)
Code:
'with translated as (
select id, CASE
WHEN colors.name = "Colors.black" THEN "' . lang('Colors.black') . '"
WHEN colors.name = "Colors.blue" THEN "' . lang('Colors.blue') . '"
WHEN colors.name = "Colors.green" THEN "' . lang('Colors.green') . '"
...
ELSE colors.name
END AS tname from colors)
SELECT *, translated.tname from routes
JOIN colors ON colors.id = routes.color_id
JOIN translated ON translated.id = colors.id
WHERE tname like "%' . $searchterm . '%"'
How does it work? It creates dynamically an additional column with translated color names for the ~10 predefined/hardcoded colors, user defined colors are kept untranslated (assuming, the user uses its native language anyway), tables are joined and the dynamic column is searched for the translated colorname.
localize databases with working searching and ordering?
