Rails: Active Record Joins And N+1

This post uses a small Rails app with three models:

class User < ApplicationRecord
  has_many :posts
  has_many :comments
end

class Post < ApplicationRecord
  belongs_to :user
  has_many :comments
end

class Comment < ApplicationRecord
  belongs_to :user
  belongs_to :post
end

A user can write many posts and comments. Each post belongs to one user and can have many comments.

The examples answer questions like:

Which users have written posts about Active Record?
Which users have zero posts?
How can posts be sorted by comment count?

These questions involve multiple tables, so they require cross-table queries. If all the needed columns live in a single table, no join is needed. A join is used when filtering, sorting, or grouping by data in another table.

§1. Inner Join: `joins`

Find users who have written posts about a particular topic:

User
  .joins(:posts)
  .where("posts.title LIKE ?", "%Active%")
  .distinct
  .order("users.name ASC")

SQL shape:

SELECT DISTINCT users.* FROM users
INNER JOIN posts ON posts.user_id = users.id
WHERE posts.title LIKE '%Active%'
ORDER BY users.name ASC;

joins brings in another table for filtering, sorting, or grouping, which allows the query to reference it in where.

distinct is needed here because one user can match multiple joined rows. If Alice has three matching posts, plain joins returns Alice three times. distinct collapses those duplicate user rows.

§2. Left Join: `left_joins`

Unlike an inner join, a left join keeps all left-side records even when no matching association exists.

Example: users sorted by post count, zero-post users included:

User
  .left_joins(:posts)
  .select("users.*, COUNT(posts.id) AS posts_count")
  .group("users.id")
  .order("posts_count DESC")

Such queries live in the controller; the results are passed to the view for presentation. With the first example, a naive view might list each user’s post titles:

<% @users.each do |user| %>
  <h2><%= user.name %></h2>
  <% user.posts.each do |post| %>
    <p><%= post.title %></p>
  <% end %>
<% end %>

This innocent-looking view causes the N+1 problem. The server log shows:

SELECT * FROM users;                            -- 1 query
SELECT * FROM posts WHERE user_id = 1;          -- N queries
SELECT * FROM posts WHERE user_id = 2;
SELECT * FROM posts WHERE user_id = 3;

Rails provides three APIs to solve this.

§3. `eager_load` - One Joined Query, Filtering Included

The first example used a join for filtering but still caused N+1 on user.posts in the view. eager_load replaces the join and preloads the association from the same query:

# Controller - replaces the earlier joins query
@users = User
  .eager_load(:posts)
  .where("posts.title LIKE ?", "%Active%")
  .order("users.name ASC")

SQL shape:

SELECT users.*, posts.* FROM users
LEFT OUTER JOIN posts ON posts.user_id = users.id
WHERE posts.title LIKE '%Active%'
ORDER BY users.name ASC;

Now the view iterates user.posts with zero additional queries. The association was loaded from the joined rows.

Because the WHERE clause filters the joined rows, user.posts contains only the matching posts. The LEFT JOIN + WHERE effectively acts as an inner filter on the loaded association.

No distinct is shown here. The SQL still returns one row per matching post, but eager_load uses those rows to build unique parent objects and attach the loaded posts. With the same data, joins may return [Alice, Alice, Alice, Carol]; eager_load returns [Alice, Carol], with Alice’s matching posts already attached.

§4. `preload` - Separate Queries, Simpler Case

When the query does not filter by the association at all, preload is the cleanest choice:

1
2
3

@users = User
  .preload(:posts)
  .order(:name)

SQL shape:

1 2	SELECT * FROM users ORDER BY name; -- 1 query SELECT * FROM posts WHERE user_id IN (...); -- 1 query

The view renders user.posts without N+1. No joined-table filtering needed; just load the association.

§5. Checking Whether An Association Is Loaded

Rails can report whether an association has already been loaded:

1	user.posts.loaded?

The lower-level equivalent is:

1	user.association(:posts).loaded?

This is a direct way to check whether the view is protected from the N+1 problem. If user.posts.loaded? is false before the view loops over user.posts, that loop can issue one extra query per user.

It is also useful when comparing joins, eager_load, and preload:

Query	`user.posts.loaded?`
`User.joins(:posts)`	`false`
`User.eager_load(:posts)`	`true`
`User.preload(:posts)`	`true`

joins can reference the posts table in SQL, but it does not load the posts association, so it does not prevent N+1 by itself. eager_load and preload do load the association, so the view can render user.posts without extra queries.

§6. Tradeoff: `eager_load` vs `preload`

There is no universal winner. eager_load may duplicate parent columns across joined rows. preload may load unnecessary associated rows. Which cost is worse depends on the concrete data.

eager_load filters in SQL and loads matching joined rows:

User
  .eager_load(:posts)
  .where("posts.title LIKE ?", "%Active%")
  .order("users.name ASC")

This can avoid loading unrelated posts, but repeated user columns appear once per matching post in the SQL result.

Example SQL result shape:

SQL row	User columns	Post columns
1	Alice data	Active post A
2	Alice data	Active post B
3	Alice data	Active post C
4	Carol data	Active post D

Waste: Alice’s user columns are repeated three times.

preload can load complete associations, then Ruby can filter in memory:

User
  .preload(:posts)
  .select do |user|
    user.posts.any? { |post| post.title.include?("Active") }
  end

This avoids repeated joined user columns, but it may load many users and posts that Ruby later discards.

Example loaded data:

Loaded record	Kept after Ruby filter?	Reason
Alice	Yes	Has an Active post
Alice’s 6 posts	Partly useful	Only 3 posts match Active
Bob	No	No Active posts
Bob’s 3 posts	No	Loaded but discarded
Dave	No	No Active posts
Dave’s 1 post	No	Loaded but discarded

Waste: extra users and posts are loaded even though Ruby later filters them out.

Approach	Waste shape	Best for
`eager_load + where`	Duplicated parent columns in joined rows	SQL-friendly filters, matching posts only
`preload + Ruby filtering`	Unnecessary parent and child rows loaded into Ruby	Small data, Ruby-only filtering logic

§7. `includes` - Convenience Wrapper

includes is a convenience helper that lets Rails choose either preload or eager_load:

Condition	Behavior
No reference to the joined table	`preload`
Hash condition on association	`eager_load`
Raw SQL referencing association + `references`	`eager_load`

1
2
3

User
  .includes(:posts)
  .where(posts: { title: "Active Record Basics" })

Using preload or eager_load directly conveys intent more clearly and avoids surprises. includes can be skipped once the two are understood.

Summary:

Method	Query strategy	Joined table in WHERE/ORDER?
`eager_load`	Single LEFT JOIN	Yes
`preload`	Separate queries	No
`includes`	Rails decides	Only when needed

§8. Advanced Example: Posts Ordered By Comment Count

Question:

1	For each user, list their posts ordered by number of comments.

This requires both SQL aggregation and Ruby-side grouping.

Controller:

@users = User
  .order("users.name ASC")

@posts_by_user_id = Post
  .left_joins(:comments)
  .select("posts.*, COUNT(comments.id) AS comments_count")
  .group("posts.id")
  .order("posts.user_id ASC, comments_count DESC")
  .group_by(&:user_id)

View:

<% @users.each do |user| %>
  <h2><%= user.name %></h2>

  <% (@posts_by_user_id[user.id] || []).each do |post| %>
    <p>
      <%= post.title %>
      (<%= post.comments_count %> comments)
    </p>
  <% end %>
<% end %>

Key details:

Code	Purpose
`left_joins(:comments)`	Keeps posts with zero comments
`COUNT(comments.id)`	Counts real comments, not the NULL row from the left join
`group("posts.id")`	Groups joined comment rows back into one row per post
`order("posts.user_id ASC, comments_count DESC")`	Keeps posts grouped by user, then sorts each user’s posts by comment count
`group_by(&:user_id)`	Ruby grouping after the SQL query, producing `{ user_id => [posts] }`

This is different from eager loading. The goal is not to render every comment; the goal is to calculate a count. Aggregation belongs in SQL.

§9. Quick Reference

Need	Reach for
Filter/sort/group by another table	`joins`
Keep records with no matches	`left_joins`
Filter by association, load it too	`eager_load`
Load association, no table refs	`preload`
Convenience (hides choice)	`includes`

§1. Inner Join: joins

§2. Left Join: left_joins

§3. eager_load - One Joined Query, Filtering Included

§4. preload - Separate Queries, Simpler Case

§5. Checking Whether An Association Is Loaded

§6. Tradeoff: eager_load vs preload

§7. includes - Convenience Wrapper

§8. Advanced Example: Posts Ordered By Comment Count

§9. Quick Reference

§1. Inner Join: `joins`

§2. Left Join: `left_joins`

§3. `eager_load` - One Joined Query, Filtering Included

§4. `preload` - Separate Queries, Simpler Case

§6. Tradeoff: `eager_load` vs `preload`

§7. `includes` - Convenience Wrapper