Rails 4 - order by translated / localized value

Question

I have a table called items with a type column. This column can have one of the following values:

rock
paper
scissor

Inside my translation file:

en: 
  rock: Stone
  paper: Wood
  scissor: Weapon

How can i fetch the results and order them by the translated value using ActiveRecord?

Obviously, if I do Item.where(something: true).order('name asc') I would get the results ordered by the value inside the database (rock) and not the translated value (Stone).

I am aware of some ruby methods such as sort_by and sort to order items with ruby, but I would like to order the results in ActiveRecord for performance reasons.

How many unique types are there? If the list is relatively short you could build up an `order` statement to do this. MySQL has a [FIELD() method](http://stackoverflow.com/a/396771/2622934) and in Postgres you could build a [case statement](http://stackoverflow.com/a/1310188/2622934). — cschroed, Jul 25 '15 at 22:14
@cschroed do you know how to solve this without knowing how many types there are? — karlingen, Jul 26 '15 at 15:02
A generic version of your answer would be something like `translation = ["rock", "paper", "scissor"].map{ |name| "WHEN type = '#{name}' THEN '#{I18n.t(name)}'" }.join(" "); Item.select("*, CASE #{translation} END AS translated_type").order("translated_type")`. But with a really huge list this might break the limits on how many `WHEN` entries are allowed in a query. — cschroed, Jul 27 '15 at 11:27
@cschroed yeah, I also thought of that but the other list consists of nearly 740 items. So much for looping through a list! — karlingen, Jul 27 '15 at 18:36
If ordering at the database level is really important you could potentially store those translations in a table that you can join with and order by. — cschroed, Jul 27 '15 at 18:40
Right. Or a table that can group these special strings by language. [This answer](http://stackoverflow.com/a/9890245/2622934) has additional suggestions. — cschroed, Jul 29 '15 at 11:28

score 6 · Accepted Answer · answered Jul 26 '15 at 14:42

I managed to solve this by using a CASE statement.

Item.select("*,
  CASE 
    WHEN type = 'rock' THEN '#{I18n.t(:rock)}' 
    WHEN type = 'paper' THEN '#{I18n.t(:paper)}' 
    WHEN type = 'scissor' THEN '#{I18n.t(:scissor)}'
  END AS translated_type
")
.where(something: true)
.order('translated_type asc')

This works fine in my case since I know which types to expect.

Rails 4 - order by translated / localized value

1 Answers1