module ActiveRecordWhereAssoc::RelationReturningMethods

This module adds new variations of #where to your Models/relations/associations/scopes. These variations check if an association has records, so you can check if a Post has any Comments.

These variations return a new relation (just like #where) so you can chain them with other scoping methods such as #where, #order, #limit, more of these variations, etc.

The arguments common to all methods are documented here at the top.

For brevity, the examples are all directly on models, such as User, Post, Comment, but the methods are available and behave the same on:

• associations: my_user.posts.where_assoc_exists(:comments)

• relations: Posts.where(serious: true).where_assoc_exists(:comments)

• scopes: (On the Post model) scope :with_comments, -> { where_assoc_exists(:comments) }

• models: Post.where_assoc_exists(:comments)

In short: Anywhere you could use #where, you can also use the methods presented here. This includes with ActiveRecord’s #or method.

Introduction to this gem introduces each features of the gem clearly.

The gem’s README.md contains known limitations and usage tips.

Many examples are available, including the generated SQL queries.

If you need extra convincing to try this gem, this document with the problems of the other ways of doing this kind of filtering should help: alternatives’ problems.

Association¶ ↑

The associations referred here are the links between your different models. They are your #belongs_to, #has_many, #has_one, #has_and_belongs_to_many.

This gem is about getting records from your database if their associations match (or don’t match) a certain condition (which by default is just to exist).

Every method here has an association_name parameter. This is the association you want to check if records exists.

# Posts with at least one comment
Post.where_assoc_exists(:comments)

# Posts with no comments
Post.where_assoc_not_exists(:comments)

If you want, you can pass an array of associations. They will be followed in order, just like a has_many :through would.

# Posts which have at least one comment with a reply
# In other words: Posts which have at least one reply reachable through his comments
Post.where_assoc_exists([:comments, :replies])

Condition¶ ↑

After the association_name argument, you can pass conditions on your association to specify which of its records you care about. For example, you could only want Posts that have a comment marked as spam, so all you care about are comments marked as spam.

Another way to look at this is that you are filtering your association (using a #where) and checking if a record of that association is still found, and you do this for each of you records.

This condition argument is passed directly to #where, so you can pass in the following:

# Posts that have at least one comment considered as spam
# Using a Hash
Post.where_assoc_exists(:comments, is_spam: true)

# Using a String
Post.where_assoc_exists(:comments, "is_spam = true")

# Using an Array (a string and its binds)
Post.where_assoc_exists(:comments, ["is_spam = ?", true])

If the condition is blank, it is ignored (just like +#where+ does).

Note, if you specify multiple associations using an Array, the conditions will only be applied to the last association.

# Users which have a post that has a comment marked as spam.
# is_spam is only checked on the comment.
User.where_assoc_exists([:posts, :comments], is_spam: true)

If you want something else, you will need to use a block (see below) to nest multiple calls.

# Users which have a post made in the last 5 days which has comments
User.where_assoc_exists(:posts) {
  where("created_at > ?", 5.days.ago).where_assoc_exists(:comments)
}

Block¶ ↑

The block is used to add more complex conditions. The effect is the same as the condition parameter. You are specifying which records in the association you care about, but using a block lets you use any scoping methods, such as #where, #joins, nested #where_assoc_*, scopes on the model, etc.

Note that using #joins might lead to unexpected results when using #where_assoc_count, since if the joins adds rows, it will change the resulting count. It probably makes more sense to, again, use one of the where_assoc_* methods (they can be nested).

There are 2 ways of using the block for adding conditions to the association.

A block that receives one argument

The block receives a relation on the target association and return a relation with added filters or may return nil to do nothing.

# These are all equivalent. Posts which have a comment marked as spam
# Using a where for the added condition
Post.where_assoc_exists(:comments) { |comments_scope| comments_scope.where(is_spam: true) }

# Applying a scope of the relation
Post.where_assoc_exists(:comments) { |comments_scope| comments_scope.spam_flagged }

# Applying a scope of the relation, using the &:shortcut for procs
Post.where_assoc_exists(:comments, &:spam_flagged)

A block that receives no argument

Instead of receiving the relation as argument, the relation is used as the “self” of the block. Everything else is identical to the block with one argument.

# These are all equivalent. Posts which have a comment marked as spam
# Using a where for the added condition
Post.where_assoc_exists(:comments) { where(is_spam: true) }

# Applying a scope of the relation
Post.where_assoc_exists(:comments) { spam_flagged }

The main reason to use a block with an argument instead of without one is when you need to call methods on the self outside of the block, such as:

Post.where_assoc_exists(:comments) { |comments| comments.where(author_id: foo(:bar)) }
Post.where_assoc_exists(:comments) { |comments| comments.where(author_id: self.foo(:bar)) }
# In both cases, using the version without arguments would not work, since the #foo
# would be called on the scope that was given to the block, instead of on the caller
# of the #where_assoc_exists method.

# THESE ARE WRONG!
Post.where_assoc_exists(:comments) { where(author_id: foo(:bar)) }
Post.where_assoc_exists(:comments) { where(author_id: self.foo(:bar)) }
# THESE ARE WRONG!

If both condition and block are given, the conditions are applied first, and then the block.

Options¶ ↑

Some options are available to tweak how queries are generated. The default values of the options can be changed globally:

# Somewhere in your setup code, such as an initializer in Rails
ActiveRecordWhereAssoc.default_options[:ignore_limit] = true

Or you can pass them as arguments after the condition argument.

Post.where_assoc_exists(:comments, "is_spam = TRUE", ignore_limit: true)
# Because this is 2 consecutive hashes, must use the +{}+
Post.where_assoc_exists(:comments, {is_spam: true}, ignore_limit: true)

Note, if you don’t need a condition, you must pass nil as condition to provide options:

Post.where_assoc_exists(:comments, nil, ignore_limit: true)

:ignore_limit option¶ ↑

When true, #limit and #offset that are set from default_scope, on associations, and from #has_one are ignored.
Removing the limit from #has_one makes them be treated like a #has_many.

Main reasons to use ignore_limit: true

• Needed for MySQL to be able to do anything with #has_one associations because MySQL doesn’t support sub-limit.
  See MySQL doesn’t support limit
  Note, this does mean the #has_one will be treated as if it was a #has_many for MySQL too.

• You have a #has_one association which you know can never have more than one record and are dealing with a heavy/slow query. The query used to deal with #has_many is less complex, and may prove faster.

• For this one special case, you want to check the other records that match your has_one

:never_alias_limit option¶ ↑

When true, #where_assoc_* will not use #from to build relations that have #limit or #offset set on default_scope or on associations or for #has_one.
This allows changing the from as part of the conditions (such as for a scope)

Main reasons to use this: you have to use #from in the block of #where_assoc_* method (ex: because a scope needs #from).

Why this isn’t the default:

• From very few tests, the aliasing way seems to produce better plans.

• Using aliasing produces a shorter query.

:poly_belongs_to option¶ ↑

Specify what to do when a polymorphic belongs_to is encountered. Things are tricky because the query can end up searching in multiple Models, and just knowing which ones to look into can require an expensive query. It’s also possible that you only want to search for those that match some specific Models, ignoring the other ones.

:pluck

Do a #pluck in the column to detect to possible choices. This option can have a performance cost for big tables or when the query if done often, as the #pluck will be executed each time. It is important to note that this happens when the query is prepared, so using this with methods that return SQL (such as SqlReturningMethods#assoc_exists_sql) will still execute a query even if you don’t use the returned string.

model or array of models

Specify which models to search for. This avoids the performance cost of #pluck and can allow to filter some of the choices out that don’t interest you.
Note, these are not instances, it’s actual models, ex: [Post, Comment]

a hash

The keys must be models (same behavior as an array of models).
The values are conditions to apply only for key’s model. The conditions are either a proc (behaves like the block, but only for that model) or the same things #where can receive. (String, Hash, Array, nil). Ex:

List.where_assoc_exists(:items, nil, poly_belongs_to: {Car => "color = 'blue'",
                                                       Computer => proc { brand_new.where(core: 4) } })

:raise

(default) raise an exception when a polymorphic belongs_to is encountered.