Query if array column contains one or more values

Question

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?

Why have you want with such field? Maybe you need to define report_id in department or make a association many to many? — AntonTkachov
– AntonTkachov, Commented Nov 14, 2017 at 16:04

hgb123 · Accepted Answer · 2020-10-01 13:27:04Z

26

Something like this should work:

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

You could find more information about array functions and operators here

edited Oct 1, 2020 at 13:27

hgb123

14.9k3 gold badges24 silver badges43 bronze badges

answered Nov 14, 2017 at 16:10

souslov

44.7k11 gold badges92 silver badges113 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Nikita Fedyashev Over a year ago

For anyone wondering what "@>" means. It is the "contains" operator in PostgreSQL. It is defined for several data types - arrays, range types, geometric types, JSON(and JSONB).

Paul Watson Over a year ago

As per other comment you might want to use && for overlap.

Tim Kozak · Accepted Answer · 2022-05-23 11:00:24Z

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)

Thank you! I don't know why they didn't include the && syntax in the docs (guides.rubyonrails.org/active_record_postgresql.html#array)

Collectives™ on Stack Overflow

Query if array column contains one or more values

2 Answers 2

2 Comments

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Related