Menu
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?
219
UNNEST takes an ARRAY and returns a table with a single row for each element in the ARRAY . Because UNNEST destroys the order of the ARRAY elements, you may wish to restore order to the table.
We can pass an array with the help of where IN clause. Let us first create a new table for our example.
To create a column of an array type, the [] symbol is used. The following examples illustrate this: create table contacts ( first_name varchar, last_name varchar, phone_numbers varchar[] ); create table player_scores ( player_number integer, round_scores integer[] );
PostgreSQL allows columns of a table to be defined as variable-length multidimensional arrays. Arrays of any built-in or user-defined base type, enum type, composite type, range type, or domain can be created.
Something like this should work:
Report.where('department_ids @> ARRAY[?]::integer[]', [2, 5])
You could find more information about array functions and operators here
67
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
