Active Record where tricks - Rails Tricks Issue 5

Hey, this week, I am bringing you Active Record tricks to find records with missing associations or with associated records only, and to negate your conditions.

The first three methods I will cover are all implemented in ActiveRecord::QueryMethods::WhereChain, which acts as a placeholder object for where queries with no direct parameter.

The first method is associated, which generates an inner join SQL query with a NOT NULL condition on the join field. For instance, if you want to find all blog posts in your database with an author, you can achieve it with the following code:

Post.where.associated(:author)
# SELECT "posts".* FROM "posts"
# INNER JOIN "authors" ON "authors"."id" = "posts"."author_id"
# WHERE "authors"."id" IS NOT NULL

The next method is missing, which does the opposite and returns the records with missing associations:

Post.where.missing(:comments)
# SELECT "posts".* FROM "posts"
# LEFT OUTER JOIN "comments" ON "comments"."post_id" = "posts"."id"
# WHERE "comments"."id" IS NULL

It is worth nothing that you can specify multiple associations to both methods, so if you would want to find all posts where the author is missing and has zero comments, you can do so by calling Post.where.missing(:author, :comments) or if you want to find all posts where the author is set and has at least one comment, you can call Post.where.associated(:author, :comments).

The third method I want to mention is not, which can be used to negate a where condition:

User.where.not(role: 'admin')
# SELECT * FROM users WHERE NOT (role = 'admin')

There is also the not so well-known invert_where method, which inverts the previous where conditions. So you can do this:

User.where(active: true).invert_where
# SELECT "users".* FROM "users" WHERE `active` != 1

You can also use it when you define scopes to not duplicate your conditions:

scope :active, -> { where(active: true) }
scope :inactive, -> { active.invert_where }

It is important to note here, invert_where inverts the where chain before the call, so if you use it with scopes, you need need to make sure the scopes are called in the correct order for the desired result.

That’s it for this week!