Heads up: Quering array columns only matches equally sorted arrays

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:

1. Sort both the query and database content. If you're on Rails 7.1 you can use the new normalizes Show archive.org snapshot macro for this. This solution would still use any indexes on the column.
2. Check for inclusion in both directions, as implemented by the 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