Read more

ActiveRecord: Specifying conditions on an associated table

Henning Koch
March 13, 2020Software engineer at makandra GmbH

We can use ActiveRecord's where Show archive.org snapshot to add conditions to a relation. But sometimes our condition is not on the model itself, but on an associated model. This card explains multiple ways to express this condition using ActiveRecord's query interface Show archive.org snapshot (without writing SQL).

Illustration online protection

Rails Long Term Support

Rails LTS provides security patches for old versions of Ruby on Rails (2.3, 3.2, 4.2 and 5.2)

  • Prevents you from data breaches and liability risks
  • Upgrade at your own pace
  • Works with modern Rubies
Read more Show archive.org snapshot

As an example we will use a User that has many Posts:

class User < ApplicationRecord
  has_many :posts
  scope :active, -> { trashed: false }
end

class Post < ApplicationRecord
  belongs_to :user
end

We now want to get all posts from active users.

Below you will find several options for doing this. It's good to know them all, but we recommend Option 0 or Option 1.

Option 0: Sub-query with conditions from a scope

You may also pass the existing User.active scope as a condition on the joined table:

Post.where(user: User.active)

This will make a single query. It uses a sub-query, which is slow in MySQL, but fast in PostgreSQL:

SELECT * FROM posts WHERE user_id IN (SELECT id FROM users WHERE trashed=f);

Option 1: Pluck foreign keys and make a second query

We can first fetch the IDs of all active users, then fetch all posts with a matching user_id:

active_user_ids = User.active.pluck(:id)
Post.where(user_id: active_user_ids)

This will make two fast queries:

SELECT id FROM users WHERE trashed=f;
SELECT * FROM posts WHERE user_id IN (3, 7, 11, 19);

Traverse with Edge Rider

Instead of plucking foreign keys manually, you may also use Edge Rider Show archive.org snapshot 's traverse_association method:

User.active.traverse_association(:posts)

This will make the same two queries as seen above:

SELECT id FROM users WHERE trashed=f;
SELECT * FROM posts WHERE user_id IN (3, 7, 11, 19);

Option 2: Use an SQL JOIN

Post.joins(:user).where('users.trashed = ?', false)

This will build a huge join table:

SELECT * FROM posts LEFT JOIN users ON users.id = posts.user_id WHERE users.trashed = f;

JOIN with hash conditions

Instead of passing a WHERE snippet you may also pass a nested hash:

Post.joins(:user).where(user: { trashed: false })

This will build the same join table:

SELECT * FROM posts LEFT JOIN users ON users.id = posts.user_id WHERE users.trashed = f;
Posted by Henning Koch to makandra dev (2020-03-13 16:51)