CODEWITHSUNDEEP
sqlruby-on-railsarrayspostgresql
xhenryx14
xhenryx14

Reputation: 754

Querying if an int is inside of an array column, Postgres with Rails

I have a table with a column of type integer array. Given an integer as input I want to query for the rows where this column named tipo_propiedad contains the input integer. Directly in Postgres that may work by using && operator and casting the input int to an array. The thing is that using this:

Zone.where("tipo_propiedad && ?", [].push(type)).count

Where type is the input integer, gives

SELECT COUNT(*) FROM "zonas" WHERE (tipo_propiedad && 1) PG::UndefinedFunction: ERROR: operator does not exist: integer[] && integer

I also tried with 

Zone.where("tipo_propiedad && ?", [5].push(type))

because tipo_propiedad would only have 1, 2 or 3 inside and that gives

SELECT COUNT(*) FROM "zonas" WHERE (tipo_propiedad && 5,1) PG::UndefinedFunction: ERROR: operator does not exist: integer[] && integer

I'm also using squeel but that gem doesn't have any operator to do this.

Upvotes: 2

Views: 1526

Answers (1)

Marth
Marth

Reputation: 24802

You can do either

Zone.where("'?' = ANY (tipo_propiedad)", 1)

or

Zone.where("tipo_propiedad && ARRAY[?]", 1)
                     # or "&& '{?}'"

Upvotes: 5

Related Questions