CODEWITHSUNDEEP
ruby-on-railsarrayspostgresqlactiverecordrails-activerecord
tommarshall
tommarshall

Reputation: 2128

Query if array column contains one or more values

I have a Rails 5 application, with a PostgreSQL 9.6 database.

The application has Report model, with a department_ids array field, which is defined in schema.rb as:

t.integer "department_ids", default: [], array: true

I need to write a query which returns report rows where the department_ids column contains one or more of a given set of department_ids.

My current workaround is to do this in Ruby with:

department_ids = [2, 5]

reports = Report.all.select do |report|
  (report.department_ids & department_ids).any?
end

However, using select has the downside of returning an Array instead of ActiveRecord::Relation, which means I need to hydrate the filtered results back into ActiveRecord::Relation objects.

Report.where(id: reports.map(&:id))

I'd like to avoid that step, and handle this all in a single query.

How can I write query like this with Active Record?

Upvotes: 20

Views: 12232

Answers (2)

Tim Kozak
Tim Kozak

Reputation: 4182

Here is my scope examples.

First one will search for at least one intersection (record column will contain at least one) Second one will require to match all (!) of the items in the array

scope :tagged_one_of, -> (tags) { tags ? where("tags && ARRAY[?]::varchar[]", tags) : all }
scope :tagged_all_of, -> (tags) { tags ? where("tags @> ARRAY[?]::varchar[]", tags) : all }

Example:

Product.where(filter).where(sub_filter).tagged_one_of(tags_array)

Upvotes: 3

potashin
potashin

Reputation: 44611

Something like this should work:

Report.where('department_ids @> ARRAY[?]::integer[]', [2, 5])

You could find more information about array functions and operators here

Upvotes: 25

Related Questions