Where Any

Helpers for using the PostgreSQL ANY() and ALL() expressions in ActiveRecord queries. This provides the functionality of WHERE IN, but in a more prepared statement friendly way.

Tested and validated only for PostgreSQL.

Add this line to your application's Gemfile:

gem 'where_any'

And then execute:

$ bundle install

Or install it yourself as:

$ gem install where_any

Then in any of your models:

class User < ApplicationRecord
  extend WhereAny

  # ...
end

Or, to install these helpers for your entire application:

class ApplicationRecord < ActiveRecord::Base
  self.abstract_class = true

  extend WhereAny

  # ...
end

To make a where ANY() query, use the where_any method:

User.where_any(:id, [1, 2, 3, 4, 5])

The first argument of where_any refers to the column being tested, and the second argument is a list of values to include in the condition.

Which would produce the following SQL:

SELECT "users".* FROM "users" WHERE "users"."id" = ANY($1)  [["id", "{1,2,3,4,5}"]

It's also possible to construct a negated where ANY() query, like so:

User.where_none(:id, [1, 2, 3, 4, 5])

Which would produce the following SQL:

SELECT "users".* FROM "users" WHERE "users"."id" != ALL($1)  [["id", "{1,2,3,4,5}"]]

The advantage of using where where_any over ActiveRecord's built-in WHERE IN support is that it produces the same SQL statement regardless of the number of elements supplied. This is advantageous when using prepared statements, as the same statement can be reused regardless of the number of inputs supplied.

Consider for example:

User.where(id: [1, 2, 3])
User.where(id: [1, 2, 3, 4])
User.where(id: [1, 2, 3, 4, 5])

# Versus

User.where_any(:id, [1, 2, 3])
User.where_any(:id, [1, 2, 3, 4])
User.where_any(:id, [1, 2, 3, 4, 5])

These sets of queries produce the following sets of SQL respectively:

SELECT "users".* FROM "users" WHERE "users"."id" IN ($1, $2, $3)  [["id", 1], ["id", 2], ["id", 3]]
SELECT "users".* FROM "users" WHERE "users"."id" IN ($1, $2, $3, $4)  [["id", 1], ["id", 2], ["id", 3], ["id", 4]]
SELECT "users".* FROM "users" WHERE "users"."id" IN ($1, $2, $3, $4, $5)  [["id", 1], ["id", 2], ["id", 3], ["id", 4], ["id", 5]]

-- Versus

SELECT "users".* FROM "users" WHERE "users"."id" = ANY($1)  [["id", "{1,2,3}"]]
SELECT "users".* FROM "users" WHERE "users"."id" = ANY($1)  [["id", "{1,2,3,4}"]]
SELECT "users".* FROM "users" WHERE "users"."id" = ANY($1)  [["id", "{1,2,3,4,5}"]]

Using the ANY() notation allows us to reuse the same query with the same number of parameter binds regardless of what number of inputs are supplied.

Using modern version of Postgres, there is no disadvantage to using ANY() from a query plan perspective.

Here is an example query plan when using where_any():

EXPLAIN for: SELECT "users".* FROM "users" WHERE "users"."id" = ANY($1) [["id", "{1,2,3,4,5}"]]
                                 QUERY PLAN
----------------------------------------------------------------------------
 Index Scan using users_pkey on users
   Index Cond: (id = ANY ('{1,2,3,4,5}'::integer[]))
(2 rows)

And here is the query plan when using ActiveRecord's WHERE IN:

EXPLAIN for: SELECT "users".* FROM "users" WHERE "users"."id" IN ($1, $2, $3, $4, $5) [["id", 1], ["id", 2], ["id", 3], ["id", 4], ["id", 5]]
                                 QUERY PLAN
----------------------------------------------------------------------------
 Index Scan using users_pkey on users
   Index Cond: (id = ANY ('{1,2,3,4,5}'::integer[]))
(2 rows)

Note how these two queries produced the exact same query plan. According to the PostgreSQL manual, these operations are equivalent: https://www.postgresql.org/docs/current/functions-subquery.html#FUNCTIONS-SUBQUERY-ANY-SOME

After checking out the repo, run bin/setup to install dependencies. Then, run rake spec to run the tests. You can also run bin/console for an interactive prompt that will allow you to experiment.

To install this gem onto your local machine, run bundle exec rake install. To release a new version, update the version number in version.rb, and then run bundle exec rake release, which will create a git tag for the version, push git commits and the created tag, and push the .gem file to rubygems.org.

Bug reports and pull requests are welcome on GitHub at https://github.com/thriver/where_any.