Posted 4 months ago. Visible to the public. Repeats.

ActiveRecord: Specifying conditions on an associated table

We can use ActiveRecord's where 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 (without writing SQL).

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

Copy
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 4 or Option 2.

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:

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

This will make two fast queries:

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

Option 2: Traverse with Edge Rider

Instead of plucking foreign keys manually, you may also use Edge Rider's traverse_association method:

Copy
User.active.traverse_association(:posts)

This will make the same two queries as seen above:

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

Option 3: Sub-query with hash conditions

You may also join the two tables, and make a where condition on the joined table:

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

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

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

Option 4: Sub-query with conditions from a scope

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

Copy
Post.joins(:user).where(user: User.active)

This will make the same query with a sub-query:

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

Option 5: Use an SQL JOIN

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

This will build a huge join table:

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

Once an application no longer requires constant development, it needs periodic maintenance for stable and secure operation. makandra offers monthly maintenance contracts that let you focus on your business while we make sure the lights stay on.

Owner of this card:

Avatar
Henning Koch
Last edit:
4 months ago
by Henning Koch
About this deck:
We are makandra and do test-driven, agile Ruby on Rails software development.
License for source code
Posted by Henning Koch to makandra dev
This website uses cookies to improve usability and analyze traffic.
Accept or learn more