ruby-on-rails - 将 postgres hstore sql 转换为 Arel

Question

我有一个 hstore users中的栏目名为 properties 的表.

如何将 where 条件中的静态 sql 字符串转换为 aRel 语法？

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

我试过了:

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

这会产生错误的 sql:

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

与我需要的相比:

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

Answer 1

您可以通过创建自己的 Arel::Nodes::InfixOperation 来使用 Arel 完成此操作:

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

将产生:

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

如果您从未听说过中缀表示法， 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.

不幸的是，Arel 的文档并不多，InfixOperation 节点的文档也没有，因此一开始可能会令人沮丧。当您正在寻找如何使用 Arel 执行特定的 SQL 操作时，最好的办法是查看 the nodes directory in the source code .