Joins, Aggregations, Raw SQL and N+1
The books page was quick with ten books. With five hundred it takes seconds, and the log scrolls with nearly identical queries. This lesson covers the queries that reach across tables — and the classic performance bug that hides in innocent-looking view code.
The rule
joins to filter, includes to display, calculate in the database.
Joins let you filter by another table's columns:
Book.joins(:reviews).where(reviews: { rating: 5 }).distinct
# books with at least one 5-star review
Book.left_outer_joins(:reviews).where(reviews: { id: nil })
# books with no reviews at alljoins makes an INNER JOIN, so a book with three matching reviews appears three times; distinct removes the repeats. joins does not load the reviews into memory.
Aggregations compute in SQL and return plain values:
Review.count # 142
Review.average(:rating) # 3.9 (a BigDecimal)
Book.sum(:pages)
Book.maximum(:pages)
Review.group(:book_id).count # { 1 => 12, 2 => 4, ... }
Book.joins(:reviews).group("books.title").average("reviews.rating")Raw SQL is there when Active Record's methods are not enough:
Book.find_by_sql(["SELECT * FROM books WHERE pages > ?", 500])Keep the ? placeholders; the injection rule still applies.
The N+1 problem:
<% @books.each do |book| %> <%# 1 query for books %>
<%= book.reviews.size %> reviews <%# +1 query per book %>
<% end %>Fifty books cost 51 queries. The fix is in the controller: @books = Book.includes(:reviews). Rails loads all the needed reviews in one more query, so the page needs 2 queries however many books there are. (preload always uses separate queries; eager_load uses one big LEFT JOIN; includes picks for you.)
The shortcut
Ask "will I show the related records, or only filter by them?" Show → includes. Filter → joins. Need a number → a calculation (count, sum, average) instead of loading rows and adding them up in Ruby.
Spot the pattern
The log shows
SELECT "books".*once, thenSELECT "reviews".* WHERE "book_id" = ?again and again.
The trap is fixing speed by caching first. Most slow Rails pages are too many queries, not slow ones.
Drills
Answer fast — trust the rule.
A view loops over 30 books and prints each book's reviews. Without eager loading, about how many queries run?
You only need books that have a 5-star review; you will not show the reviews. What fits?
What does Review.group(:book_id).count return?
Which is the best way to get the average rating of all reviews?
How do you run raw SQL safely with a user-supplied minimum page count?
Remember
- joins to filter, includes to display, calculate in the database.
- Shortcut: show the records → includes; only filter → joins; need a number → a calculation.
- Trap: the N+1 query hiding in a view loop.