ActiveRecord’s queries tricks

Published in

Ruby Inside

3 min readAug 21, 2017

My favourite part of Rails is clearly ActiveRecord’s scopes. Their expressiveness and their reusability is simply great.

You’ll see below five tricks I usually bring during consulting missions.

1) Join query with condition on the associated table

Say you have a users table, with a profile association.
If you need to query users which profile is validated you may end up doing:

# User model
scope :activated, ->{
  joins(:profile).where(profiles: { activated: true })
}

I feel like this is a wrong approach: Profile logic is now leaking inside the User model. This is against our Object Oriented encapsulation rules.

I would suggest the following approach:

# Profile model
scope :activated, ->{ where(activated: true) }# User model
scope :activated, ->{ joins(:profile).merge(Profile.activated) }

With this setup you keep separated concerns and logic.

2) Different nested joins

beware of the way you use joins in ActiveRecord, say User has one Profile, and Profile has many Skills. By default it uses INNER JOIN but…


User.joins(:profiles).merge(Profile.joins(:skills))
=> SELECT users.* FROM users 
   INNER JOIN profiles    ON profiles.user_id  = users.id
   LEFT OUTER JOIN skills ON skills.profile_id = profiles.id# So you'd rather use:User.joins(profiles: :skills)
=> SELECT users.* FROM users 
   INNER JOIN profiles ON profiles.user_id  = users.id
   INNER JOIN skills   ON skills.profile_id = profiles.id

For tips about how to get a bullet proof app, see my upcoming book:

The Unhappy Path

Failure Management Patterns in Ruby and Rails.

leanpub.com

3) Exist query

Say you need to get users without famous posts associated and you head towards a NOT EXISTS query. You can write it elegantly with the convenience methods below.

# Post
scope :famous, ->{ where("view_count > ?", 1_000) }# User
scope :without_famous_post, ->{
  where(_not_exists(Post.where("posts.user_id = users.id").famous))
}def self._not_exists(scope)
  "NOT #{_exists(scope)}"
enddef self._exists(scope)
  "EXISTS(#{scope.to_sql})"
end

You can follow the same pattern for EXISTS.

4) Subqueries

Let’s say you need to get posts written by a subset of users.
I have seen people abusing pluck this way.

Post.where(user_id: User.created_last_month.pluck(:id))

The flaw here is two SQL queries would be ran: one to fetch the ids of the users, another one to get the posts from these user_ids.
You could achieve the same result with a single query containing a subquery:

Post.where(user_id: User.created_last_month)

ActiveRecord handles it for you 👍

5) Back to basics

Do not forget ActiveRecord queries can be appended with .to_sql to generate SQL string and by .explain to get details, complexity estimates etc...

6) Booleans

I guess you expect User.where.not(tall: true) to generate SELECT users.* FROM users WHERE users.tall <> 't' (postgres version).

This would return users where tall is set to false but NOT those where tall is set to NULL .

You would have to write: User.where("users.tall IS NOT TRUE") or User.where(tall: [false, nil]) .

Thanks Tomasz Ras for proofreading!