CODEWITHSUNDEEP
ruby-on-railspostgresqlactiverecord
angkiki
angkiki

Reputation: 495

Rails query comparing against Postgres Array Column

So i have a this column in a table t.text "foo", default: [], array: true

And i have this array list in a helper file, 

module HelperData

  Helper_array = [
    "data_1",
    "data_2",
    "data_3",
    "data_4",
    "data_5"
  ]
end

Inside the FooName.rb model there's a line include HelperData.

So the query i want to pass is the include operator && in comparing PG arrays, based on this Postgres Guide.

I have tried the following queries:

FooName.where("foo" && HelperData::Helper_array). This query returns the error ActiveRecord::StatementInvalid: PG::SyntaxError: ERROR:. The syntax error is with the elements in HelperData::Helper_array. I have a slight hunch that it is because Foo is :text but the data in Helper_array is a string, though i can't be certain.

FooName.where("foo && HelperData::Helper_array"). This query returns the error ActiveRecord::StatementInvalid: PG::UndefinedColumn: ERROR:. Obviously this is just the wrong syntax then.

What is the proper query to find whether the foo array has any elements that overlap with the Helper_array, and return all FooName objects that has overlapping elements?

Upvotes: 1

Views: 1616

Answers (1)

seaify - Freelancer
seaify - Freelancer

Reputation: 748

try:

FooName.where("foo && ARRAY[?]::text[]", HelperData::Helper_array)

refer this link https://www.postgresql.org/docs/current/static/functions-array.html

Upvotes: 2

Related Questions