ActiveRecord: Specifying conditions on an associated table

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).

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

Copyclass 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:

CopyPost.where(user: User.active)

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

CopySELECT * 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:

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

This will make two fast queries:

CopySELECT 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:

CopyUser.active.traverse_association(:posts)

This will make the same two queries as seen above:

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

Option 2: Use an SQL JOIN

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

This will build a huge join table:

CopySELECT * 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:

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

This will build the same join table:

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