ActiveRecord querying on Postgres array column
On a Rails project I’m working on, we have a model that has a db column of array datatype. This is one of those Postgres special datatypes that isn’t present on many other SQL databases, and there’s even some contention about whether it should be an option at all.At any rate, this is deeply ingrained in our project currently, so I don’t want to go reinventing the wheel at this point and convert this particular column to a separate model and create another has_many/belongs_to relationship. All I want is a way to find all of the objects in this model that have an empty array in that column.
Turns out there’s no built in ActiveRecord finder for this, but the raw SQL isn’t too terrible:
Model.where("NULL = ALL(array_column_name)")
Hat Tip: https://coderwall.com/p/sud9ja/rails-4-the-postgresql-array-data-type