Sorting an ActiveRecord relation by PostgreSQL hstore value with Arel

For the past 2 days I was struggling with sorting an ActiveRecord relation by a value in a hstore hash.

I currently work on a project that is a product catalog. Every attribute of the Product model had to be translated into multiple languages. So I picked the hstore_translations gem. In a nutshell the gem stores translations of attributes in PostgreSQL hstore columns.

In the frontend layer of the app, there is some sorting by the name. In plain SQL this would look like this:

SELECT products.* FROM products AS products ORDER BY products.name_translations -> 'en' DESC  

And this works. The problem appears, when you try to do this in Rails:

Product.order("name_translations -> 'en'" => :desc)  

This fails miserably with the following error:

: SELECT "products".* FROM "products"  ORDER BY "products"."name_translations -> 'en'" DESC
ActiveRecord::StatementInvalid: PG::UndefinedColumn: ERROR:  column products.name_translations -> 'en' does not exist  
LINE 1: SELECT "products".* FROM "products"  ORDER BY "products"."na...  

The key in this error is "products"."name_translations -> 'en'". Rails treats the value extraction syntax of PostgreSQL as a column name and adds quotation marks around it. Because of this PostgreSQL thinks that this is a a column name so it spits out this error.

The trick here, is to use Arel:

at = Product.arel_table  
node = Arel::Nodes::InfixOperation.new('->', at[:name_translations], Arel::Nodes.build_quoted(:en))  
Product.select([at[Arel.star], node.as('name')]).order('name DESC')  

The Infix operation is used in arithmetical and logical statements (read more).

Basically what this code does, it creates an Arel node that represents the hstore value extraction and selects it as name alongside all of the columns - at[Arel.star].

I could do only Arel.star, but then the * in the SELECT wouldn't be scoped to the products table and if you joined in a table that has the same column names, the values would be overriden.

It is also crucial that, the argument to order is a string, and not a hash, because when you will try to make an additional join, Rails will add the table name (products) in front of name and PostgreSQL will complain again about an unknown column.

Running to_sql on this generates a valid SQL:

SELECT "products".*, "products"."name_translations" -> 'en' AS name FROM "products"  ORDER BY name DESC  

I could solve this by just writing a SQL statement and using find_by_sql. Byt maintaining SQL strings inside of code is hard, and it looks ugly. The Arel approach has an OO feel to it.