I have a Rails 5 application, with a PostgreSQL 9.6 database.
我有一个Rails 5应用程序，带有PostgreSQL 9.6数据库。
The application has Report model, with a department_ids array field, which is defined in schema.rb as:
该应用程序具有Report模型，其中包含department_ids数组字段，该字段在schema.rb中定义为：
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.
我需要编写一个返回报告行的查询，其中department_ids列包含一组或多组给定的department_id。
My current workaround is to do this in Ruby with:
我目前的解决方法是在Ruby中执行以下操作：
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.
但是，使用select具有返回Array而不是ActiveRecord :: Relation的缺点，这意味着我需要将过滤后的结果水合回到ActiveRecord :: Relation对象中。
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?
如何用Active Record编写这样的查询？
1 个解决方案
#1
7
Something like this should work:
像这样的东西应该工作：
Report.where('department_ids @> ARRAY[?]::integer[]', [2, 5])
#1
7
Something like this should work:
像这样的东西应该工作：
Report.where('department_ids @> ARRAY[?]::integer[]', [2, 5])