Eager Loading N+1 issue

Question

 <%- @lessons.includes(:bookmarks).each_with_index do |lesson, index| -%>
          <tr>
            <td class="w6 pl">
              <%- bookmark = lesson.bookmarks.where(user_id: current_user).first -%>
              <%= render :partial => "student/lessons/bookmark", :locals => {:bookmark => bookmark, :lesson => lesson} %>
            </td>
          </tr>
 <%- end -%>

The above code produces the following N+1 queries:

  Bookmark Load (0.3ms)  SELECT "bookmarks".* FROM "bookmarks" WHERE "bookmarks"."lesson_id" IN (1, 2, 3, 4, 5, 6)
  Bookmark Load (0.3ms)  SELECT "bookmarks".* FROM "bookmarks" WHERE "bookmarks"."lesson_id" = 1 AND "bookmarks"."user_id" = 1 LIMIT 1
Rendered student/lessons/_bookmark.html.erb (1.4ms)
  Bookmark Load (0.3ms)  SELECT "bookmarks".* FROM "bookmarks" WHERE "bookmarks"."lesson_id" = 2 AND "bookmarks"."user_id" = 1 LIMIT 1
Rendered student/lessons/_bookmark.html.erb (0.6ms)
  Bookmark Load (0.3ms)  SELECT "bookmarks".* FROM "bookmarks" WHERE "bookmarks"."lesson_id" = 3 AND "bookmarks"."user_id" = 1 LIMIT 1
Rendered student/lessons/_bookmark.html.erb (0.6ms)
  Bookmark Load (0.3ms)  SELECT "bookmarks".* FROM "bookmarks" WHERE "bookmarks"."lesson_id" = 4 AND "bookmarks"."user_id" = 1 LIMIT 1
Rendered student/lessons/_bookmark.html.erb (0.6ms)
  Bookmark Load (0.2ms)  SELECT "bookmarks".* FROM "bookmarks" WHERE "bookmarks"."lesson_id" = 5 AND "bookmarks"."user_id" = 1 LIMIT 1
Rendered student/lessons/_bookmark.html.erb (0.6ms)
  Bookmark Load (0.1ms)  SELECT "bookmarks".* FROM "bookmarks" WHERE "bookmarks"."lesson_id" = 6 AND "bookmarks"."user_id" = 1 LIMIT 1

How do I prevent the additionally queries after the first one since I'm using a where query inside the loop?

Thanks!

`@lessons.includes(:bookmarks).where(bookmarks: {user_id: current_user.id}).each_with_index` and then `bookmark = lesson.bookmarks.first`? — fl00r, Jun 13 '12 at 20:14
EDIT: Actually this query will then only return lessons with bookmarks. I want to return all lessons, but be able to know if the lesson has a bookmark or not and get the bookmark if the lesson has one. — John, Jun 13 '12 at 20:24
so you can't use eager loading in your case. you need to do two queries manually — fl00r, Jun 13 '12 at 20:39

fl00r · Accepted Answer · 2012-06-13T21:04:46.997

2

you need to do two queries manually:

@lessons = Lesson.all # i.e.
@bookmarks = Bookmark.select("bookmarks.*, DISTINCT(lesson_id)").where(user_id: current_user.id)

And then

@lessons.each do |lesson|
  ...
  bookmark = @bookmarks.detect{ |b| b.lesson_id == lesson.id }
  ...
end

edited Jun 13 '12 at 21:04

answered Jun 13 '12 at 20:42

fl00r

82,987
33
217
237

Getting a postgres error for using .group(:lesson_id) SELECT "bookmarks".* FROM "bookmarks" WHERE "bookmarks"."user_id" = 1 GROUP BY bookmarks.lesson_id ActiveRecord::StatementInvalid: PGError: ERROR: column "bookmarks.id" must appear in the GROUP BY clause or be used in an aggregate function – John Jun 13 '12 at 20:58
Oh, postgre. You can try use `DISTINCT` instead of `GROUP BY`. I'll update my answer. And I am not sure if it will work with postgre :) – fl00r Jun 13 '12 at 21:04
@fl00r: The name of the RDBMS is `PostgreSQL` or `Postgres` for short. Ain't no such thing as "postgre". – Erwin Brandstetter Jun 16 '12 at 04:10

Eager Loading N+1 issue

1 Answers1