ruby-on-rails - 您如何使用变量在 ruby​​ on rails 中查询 postgres 数组的整数？

Question

Notification.where("uip  @> ?", '{1}')

工作正常并返回其 uip 数组包含 1 的所有通知。

但是，如果我尝试使用变量执行以下操作，我就没有这样的运气了:

ip = 1

Notification.where("uip  @> ?", '{ip}')

返回错误:

Notification Load (1.8ms)  SELECT "notifications".* FROM "notifications"  WHERE (uip  @> '{ip}')
PG::InvalidTextRepresentation: ERROR:  invalid input syntax for integer: "ip"
LINE 1: ...otifications".* FROM "notifications"  WHERE (uip  @> '{ip}')
                                                                ^
: SELECT "notifications".* FROM "notifications"  WHERE (uip  @> '{ip}')
ActiveRecord::StatementInvalid: PG::InvalidTextRepresentation: ERROR:  invalid input syntax for integer: "ip"
LINE 1: ...otifications".* FROM "notifications"  WHERE (uip  @> '{ip}')
                                                                ^
: SELECT "notifications".* FROM "notifications"  WHERE (uip  @> '{ip}')

还有一次尝试:

Notification.where("uip  @> ?", ip)

给出错误:

SELECT "notifications".* FROM "notifications"  WHERE (uip  @> 1)
PG::UndefinedFunction: ERROR:  operator does not exist: bigint[] @> integer
LINE 1: ...CT "notifications".* FROM "notifications"  WHERE (uip  @> 1)
                                                                  ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.
: SELECT "notifications".* FROM "notifications"  WHERE (uip  @> 1)
ActiveRecord::StatementInvalid: PG::UndefinedFunction: ERROR:  operator does not exist: bigint[] @> integer
LINE 1: ...CT "notifications".* FROM "notifications"  WHERE (uip  @> 1)
                                                                  ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.
: SELECT "notifications".* FROM "notifications"  WHERE (uip  @> 1)

那么我怎样才能简单地通过 rails 中 postgres 数组中的整数变量找到对象呢？

谢谢!

Answer 1

有多种方法可以使用 ActiveRecord 在 postgres 数组中查询:

修复您的查询:

ip = 1    
Notification.where("uip @> '{?}'", ip)

使用“任何”:

ip = 1
Notification.where("uip && ARRAY[?]", ip)