Given you have an array column like this:
create_table "users", force: :cascade do |t|
t.integer "movie_ids", default: [], array: true
end
You might think that the following queries yield the same result:
User.where(movie_ids: [16, 17])
User.where(movie_ids: [17, 16])
Turn's out - they are not! They do care about array ordering more than I do.
To query for identical arrays independent of their order you have to either:
normalizes
Show archive.org snapshot
macro for this. This solution would still use any indexes on the column.where_array_matches
method below. Note that it does not care about duplicates - [1, 2, 2]
is handled identically to [1, 2]
:# lib/ext/active_record/relation.rb
class ActiveRecord::Relation
def where_array_matches!(array_attribute, expected_values)
quoted_column = ActiveRecord::Base.connection.quote_column_name(array_attribute)
where!("#{quoted_column} @> array[:expected_values] AND #{quoted_column} <@ array[:expected_values]", expected_values:)
end
end
# app/models/application_record.rb
class ApplicationRecord < ActiveRecord::Base
def self.where_array_matches(array_attribute, expected_values)
quoted_column = ActiveRecord::Base.connection.quote_column_name(array_attribute)
where("#{quoted_column} @> array[:expected_values] AND #{quoted_column} <@ array[:expected_values]", expected_values:)
end
end
..and include their specs to your test suite:
describe ApplicationRecord do
describe '.where_array_matches!' do
let!(:matching_user) { create(:user, movie_ids: [1, 2, 3]) }
it 'matches records with exactly the same values' do
expect(User.where_array_matches(:movie_ids, [1, 2, 3])).to include(matching_user)
end
it 'is order-independent' do
expect(User.where_array_matches(:movie_ids, [2, 1, 3])).to include(matching_user)
expect(User.where_array_matches(:movie_ids, [3, 2, 1])).to include(matching_user)
end
it 'does not match if the given value only partially overlaps with queried values' do
expect(User.where_array_matches(:movie_ids, [1, 2])).to be_empty
expect(User.where_array_matches(:movie_ids, [1, 2, 3, 4])).to be_empty
end
end
end
describe ActiveRecord::Relation do
describe '#where_array_matches!' do
let!(:matching_user) { create(:user, movie_ids: [1, 2, 3]) }
it 'matches records with exactly the same values' do
expect(User.all.where_array_matches!(:movie_ids, [1, 2, 3])).to include(matching_user)
end
it 'is order-independent' do
expect(User.all.where_array_matches!(:movie_ids, [2, 1, 3])).to include(matching_user)
expect(User.all.where_array_matches!(:movie_ids, [3, 2, 1])).to include(matching_user)
end
it 'does not match if the given value only partially overlaps with queried values' do
expect(User.all.where_array_matches!(:movie_ids, [1, 2])).to be_empty
expect(User.all.where_array_matches!(:movie_ids, [1, 2, 3, 4])).to be_empty
end
end
end