ActiveRecord::WhereExists

Filters ActiveRecord records by the existence of associated records, using correlated EXISTS subqueries.

Add this line to your application's Gemfile:

gem 'active_record-where_exists'

And then execute:

$ bundle

Then extend your models with ActiveRecord::WhereExists (typically in ApplicationRecord, so all models get it):

class ApplicationRecord < ActiveRecord::Base
  primary_abstract_class

  extend ActiveRecord::WhereExists
end

Given:

class User < ApplicationRecord
  has_many :orders
  has_many :completed_orders, -> { completed }, class_name: 'Order'
end

where.exists filters records to those with associated records matching a relation:

User.where.exists(orders: Order.completed)
# SELECT "users".* FROM "users" WHERE EXISTS (
#   SELECT 1 FROM "orders" WHERE "orders"."status" = 'completed' AND "orders"."user_id" = "users"."id"
# )

And where.not_exists filters records to those without them:

User.where.not_exists(orders: Order.completed)

Associations given by name match any associated record (ie. exists(:orders) is the same as exists(orders: Order.all)):

User.where.exists(:orders)
User.where.not_exists(:orders)

Several associations can be given at once. Records must match all of them with exists, and none of them with not_exists:

User.where.exists(:addresses, orders: Order.completed)

The subquery is constrained in the same way as joining the association (ie. joins(:association)). So it applies the scope of the association, as well as the default scope of the associated model:

User.where.exists(completed_orders: Order.where(total: 100..))
# SELECT "users".* FROM "users" WHERE EXISTS (
#   SELECT 1 FROM "orders"
#   WHERE "orders"."total" >= 100.0 AND "orders"."status" = 'completed' AND "orders"."user_id" = "users"."id"
# )

It also respects extensions to how associations are joined, such as the filtering of soft-deleted records by SoftDeletable.

Note that, as with joins, ordering in the scope of a has_one association is ignored. So exists(latest_order: Order.completed) matches records with any completed order.

Associated records can also be filtered by a proc, which is given a relation of the associated records:

User.where.exists(orders: -> (orders) { orders.completed })

A proc is needed for self-referential associations (or any association to the table of the records being filtered), where the associated records are referred to by an alias, so that their conditions apply to the alias:

class User < ApplicationRecord
  has_many :friendships
  has_many :friends, through: :friendships
end

User.where.exists(friends: -> (friends) { friends.where(name: 'Alice') })
# SELECT "users".* FROM "users" WHERE EXISTS (
#   SELECT 1 FROM "users" AS "friends_users"
#   INNER JOIN "friendships" ON "friends_users"."id" = "friendships"."friend_id"
#   WHERE "friends_users"."name" = 'Alice' AND "friendships"."user_id" = "users"."id"
# )

Relations that refer to the table of the records being filtered (eg. User.where.exists(friends: User.all), or User.where.exists(orders: Order.joins(:user))) raise an ArgumentError, since their conditions would be ambiguous between the associated records and the records being filtered.

All kinds of associations are supported, including has_many :through, has_and_belongs_to_many, and self-referential associations.

Polymorphic belongs_to associations need a relation (or model), which specifies the type of the associated record:

class Comment < ApplicationRecord
  belongs_to :commentable, polymorphic: true
end

Comment.where.exists(commentable: Post.published)
Comment.where.exists(commentable: Post)

The subquery selects 1 (so the database doesn't need to read the rows of the associated table), unless the relation has its own projection, which is kept:

User.where.exists(orders: Order.select(:user_id).group(:user_id).having('SUM(total) > 100'))

A Tapioca DSL compiler is included, which adds the exists and not_exists methods to the where-chains generated for models extended by ActiveRecord::WhereExists. It's loaded automatically by bin/tapioca dsl.

The specs run against a dummy Rails application in spec/dummy, using SQLite by default:

$ bin/rspec
$ bin/rubocop

PostgreSQL and MySQL are selected with the DATABASE_ADAPTER and DATABASE_URL environment variables (see .github/workflows/ci.yml).

The gem is available as open source under the terms of the MIT License.