EugZol / where_exists

Adds the power of SQL Exists to ActiveRecord
MIT License
110 stars 18 forks source link

Where Exists

Rails way to harness the power of SQL EXISTS condition
Gem Version

Description

Exists

This gem does exactly two things:

It uses SQL EXISTS condition to do it fast, and extends ActiveRecord with where_exists and where_not_exists methods to make its usage simple and straightforward.

Quick start

Add gem to Gemfile:

gem 'where_exists'

and run bundle install as usual.

And now you have where_exists and where_not_exists methods available for your ActiveRecord models and relations.

Syntax:

Model.where_exists(association, additional_finder_parameters)

Supported Rails versions: >= 5.2.

Example of usage

Given there is User model:

class User < ActiveRecord::Base
  has_many :connections
  has_many :groups, through: :connections
end

And Group:

class Group < ActiveRecord::Base
  has_many :connections
  has_many :users, through: :connections
end

And standard many-to-many Connection:

class Connection
  belongs_to :user
  belongs_to :group
end

What I want to do is to:

Also, I don't want to:

If you wonder how to do that without the gem (i.e. essentially by writing SQL EXISTS statement manually) see that StackOverflow answer (disclosure: it's self-answered question of a contributor of this gem).

And now you are able to do all these things (and more) as simple as:

Select only users who don't belong to given set of Groups (groups with ids [4,5,6])

# It's really neat, isn't it?
User.where_exists(:groups, id: [4,5,6])

Notice that the second argument is where parameters for Group model

Select only users who belong to one set of Groups ([1,2,3]) and don't belong to another ([4,5,6])

# Chain-able like you expect them to be.
#
# Additional finder parameters is anything that
# could be fed to 'where' method.
#
# Let's use 'name' instead of 'id' here, for example.

User.where_exists(:groups, name: ['first','second','third']).
  where_not_exists(:groups, name: ['fourth','fifth','sixth'])

It is possible to add as much attributes to the criteria as it is necessary, just as with regular where(...)

Select only users who don't belong to a Group

# And that's just its basic capabilities
User.where_not_exists(:groups)

Adding parameters (the second argument) to where_not_exists method is feasible as well, if you have such requirements.

Re-use existing scopes

User.where_exists(:groups) do |groups_scope|
  groups_scope.activated_since(Time.now)
end

User.where_exists(:groups, &:approved)

If you pass a block to where_exists, the scope of the relation will be yielded to your block so you can re-use existing scopes.

Additional capabilities

Q: Does it support both has_many and belongs_to association type?
A: Yes.

Q: Does it support polymorphic associations?
A: Yes, both ways.

Q: Does it support multi-level (recursive) :through associations?
A: You bet. (Now you can forget complex EXISTS or JOIN statetements in a pretty wide variety of similar cases.)

Q: Does it support where parameters with interpolation, e.g. parent.where_exists(:child, 'fieldA > ?', 1)?
A: Yes.

Q: Does it take into account default association condition, e.g. has_many :drafts, -> { where published: nil }?
A: Yes.

Contributing

If you find that this gem lacks certain possibilities that you would have found useful, don't hesitate to create a feature request.

Also,

Please ping me in addition to creating PR/issue (just add "@EugZol" to the PR/issue text). Thank you!

To run tests:

> bundle exec appraisal install
> bundle exec appraisal rake test

License

This project uses MIT license. See MIT-LICENSE file for full text.

Alternatives

One known alternative is https://github.com/MaxLap/activerecord_where_assoc

A comprehensive comparison is made by MaxLap here: https://github.com/MaxLap/activerecord_where_assoc/blob/master/ALTERNATIVES_PROBLEMS.md