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:
- Sort both the query and database content. If you're on Rails 7.1 you can use the new
normalizesShow archive.org snapshot macro for this. This solution would still use any indexes on the column. - Check for inclusion in both directions, as implemented by the
where_array_matchesmethod 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
Posted by Michael Leimstädtner to makandra dev (2023-11-03 11:01)