CODEWITHSUNDEEP
ruby-on-railsrubyarrayspostgresqlruby-on-rails-4
Laser
Laser

Reputation: 5439

How do you query postgres arrays in ruby on rails for integers using a variable?

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

Works fine and returns all the notifications whose uip array contain a 1.

If I try the following with a variable, however, I have no such luck:

ip = 1

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

Return the error:

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}')

And another attempt with:

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

Gives the error:

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)

So how can I simply find objects by an integer variable inside a postgres array in rails?

Thanks!

Upvotes: 1

Views: 1393

Answers (4)

Angu
Angu

Reputation: 872

Use it. i think this is helpful for you

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

OR

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

Upvotes: 0

Rustam Gasanov
Rustam Gasanov

Reputation: 15781

Try this one. I just moved your braces into condition:

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

Upvotes: 1

pozs
pozs

Reputation: 36224

You need to create an array from your input:

Notification.where("uip @> CAST(ARRAY[?] AS BIGINT[])", ip)
// or
Notification.where("uip @> CAST(? AS BIGINT[])", '{' + ip.to_s + '}')
// or
Notification.where("uip @> CAST('{' || ? || '}' AS BIGINT[])", ip)

If you want to test only one element, you can use the overlap (&&) operator too (which should be faster, a little). Or, you can use the ANY construct for arrays:

Notification.where("? = ANY (uip)", ip)

Upvotes: 2

sandeep
sandeep

Reputation: 521

There are multiple ways you can query in a postgres array using ActiveRecord:

Fixing your query:

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

Using 'ANY':

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

Upvotes: 4

Related Questions