Search code examples
ruby-on-railsarelhstore

Convert postgres hstore sql to Arel

I have a hstore column in the users table named properties.

How can I convert the static sql string inside the where condition to aRel syntax?

User.where("properties -> 'is_robber' = 'true'") #=> ...some users

I have tried:

ut = User.arel_table
condition = ut["properties -> 'is_robber'"].eq('true')
User.where(condition) #=> throws pg error

And that produces wrong sql:

 SELECT "users".* FROM "users" WHERE "users"."properties -> 'is_robber'" = 'true'

Compared to what i need:

SELECT "users".* FROM "users" WHERE "users".properties -> 'is_robber' = 'true'
Solution

  • You can accomplish this with Arel by creating your own Arel::Nodes::InfixOperation:

    ut = Arel::Table.new(:user)
hstore_key = Arel::Nodes::InfixOperation.new("->", ut[:properties], 'is_robber')
User.where(hstore_key.eq('true'))

    Will produce:

    SELECT "users".* FROM "users" WHERE "users"."properties" -> 'is_robber' = 'true'

    If you've never heard of infix notation, Wikipedia gives a good explaination:

    Infix notation is the common arithmetic and logical formula notation, in which operators are written infix-style between the operands they act on (e.g. 2 + 2). It is not as simple to parse by computers as prefix notation ( e.g. + 2 2 ) or postfix notation ( e.g. 2 2 + ), but many programming languages use it due to its familiarity.

    Unfortunately, there's not much documentation for Arel, and none for the InfixOperation node, so it can be frustrating to start out with. When you're looking for how to perform particular SQL operations with Arel, your best bet is to look through the nodes directory in the source code.