Read more

Heads up: Quering array columns only matches equally sorted arrays

Avatar
Michael Leimstädtner
November 03, 2023Software engineer at makandra GmbH

Given you have an array column like this:

create_table "users", force: :cascade do |t|
  t.integer "movie_ids", default: [], array: true
end
Illustration online protection

Rails professionals since 2007

Our laser focus on a single technology has made us a leader in this space. Need help?

  • We build a solid first version of your product
  • We train your development team
  • We rescue your project in trouble
Read more Show archive.org snapshot

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

Avatar
Michael Leimstädtner
November 03, 2023Software engineer at makandra GmbH
Posted by Michael Leimstädtner to makandra dev (2023-11-03 12:01)