Query if array column contains one or more values

user_885540 0
Viewed 8018

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?

rails-activerecord postgresql activerecord ruby-on-rails arrays
2 Answers

Something like this should work:

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

You could find more information about array functions and operators here

user_34442400

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)
user_7887980
Related