Eager loading comments associated with user submissions

Rails
1

Hey everyone,

I am working on an application that allows users to submit articles,
and also comment on those articles. Pretty much exactly like Digg.
Everything works fine except that when there are a lot of comments
there are a lot of database queries to both fetch the comments and the
users who posted those comments. I have used eager loading in other
parts of my application to reduce the amount of database queries, but
when I apply the same practices in this situation it doesn’t seem to
work. Here is what I am working with:

I am using the “acts_as_commentable_with_threading” plugin that I
forked from elight on github

the association that is created is as follows

class Submission < ActiveRecord::Base
has_many :comment_threads, :class_name => “Comment”, :as
=> :commentable, :dependent => :destroy, :order => ‘created_at ASC’
end

So Submission has many comment_threads… using this association I
tried to implement eager loading like this:

def show
@submission = Submission.find(params[:id], :include =>
{:comment_threads => :user})
end

I tried it without the hash, and just the comment_threads as well

In the view, I am rendering the comment partials by calling

render :partial => @submission.comment_threads

but it still makes a separate database call for each comment.

Anything obvious I am doing wrong hear? I am getting a bit frustrated
because a simple page with 15 comments loads 2X more slowly than a
submissions page that has images, and many other things going on with
it.

Thanks for the help!

2

On Mar 11, 12:15 pm, cdubd [email protected] wrote:

render :partial => @submission.comment_threads

but it still makes a separate database call for each comment.

What database calls (ie what is the association that is getting loaded
1 at a time ?)

Fred

3

On Mar 11, 3:44 pm, Frederick C. [email protected]
wrote:

On Mar 11, 12:15 pm, cdubd [email protected] wrote:> render :partial => @submission.comment_threads

but it still makes a separate database call for each comment.

What database calls (ie what is the association that is getting loaded
1 at a time ?)

Here is the output to the log. You can see at the bottom, that when
the comment partials are rendered, there is a SELECT call to the
database for each comment.

Processing SubmissionsController#show (for 127.0.0.1 at 2009-03-11
12:50:20) [GET]
Parameters: {“id”=>“4-university-offers-beatles-degree”}
Submission Columns (2.2ms) SHOW FIELDS FROM submissions
Submission Load (0.9ms) SELECT * FROM submissions WHERE
(submissions.id = 4)
User Columns (3.2ms) SHOW FIELDS FROM users
User Load (1.2ms) SELECT * FROM users WHERE (users.id = 1)
Image Load (0.5ms) SELECT images.* FROM images WHERE
(images.submission_id = 4)
Comment Load (1.5ms) SELECT comments.* FROM comments WHERE
(comments.commentable_id = 4 and comments.commentable_type =
‘Submission’) ORDER BY created_at ASC
CACHE (0.0ms) SELECT * FROM users WHERE (users.id = 1)
Rendering template within layouts/application
Rendering submissions/show
Rendered submissions/_submission (3.4ms)
Comment Load (1.3ms) SELECT * FROM comments WHERE
(comments.commentable_id = 4 AND comments.commentable_type =
‘Submission’ AND (comments.parent_id IS NULL)) ORDER BY created_at
ASC
CACHE (0.0ms) SELECT * FROM comments WHERE
(comments.commentable_id = 4 AND comments.commentable_type =
‘Submission’ AND (comments.parent_id IS NULL)) ORDER BY created_at
ASC
CACHE (0.0ms) SELECT * FROM users WHERE (users.id = 1)
User Load (0.7ms) SELECT * FROM users WHERE (users.id = 3)
Comment Load (0.9ms) SELECT * FROM comments WHERE
(comments.id = 7)
Rendered comments/_comment (62.6ms)
CACHE (0.0ms) SELECT * FROM users WHERE (users.id = 1)
Comment Load (0.8ms) SELECT * FROM comments WHERE
(comments.id = 8)
Rendered comments/_comment (33.0ms)
CACHE (0.0ms) SELECT * FROM users WHERE (users.id = 1)
Comment Load (0.8ms) SELECT * FROM comments WHERE
(comments.id = 9)
Rendered comments/_comment (46.4ms)
CACHE (0.0ms) SELECT * FROM users WHERE (users.id = 1)
Comment Load (0.9ms) SELECT * FROM comments WHERE
(comments.id = 10)
Rendered comments/_comment (33.2ms)
CACHE (0.0ms) SELECT * FROM users WHERE (users.id = 1)
Comment Load (0.9ms) SELECT * FROM comments WHERE
(comments.id = 11)
Rendered comments/_comment (32.2ms)
CACHE (0.0ms) SELECT * FROM users WHERE (users.id = 1)
Comment Load (0.8ms) SELECT * FROM comments WHERE
(comments.id = 12)
Rendered comments/_comment (33.9ms)
CACHE (0.0ms) SELECT * FROM users WHERE (users.id = 1)
Comment Load (0.8ms) SELECT * FROM comments WHERE
(comments.id = 13)
Rendered comments/_comment (33.5ms)
CACHE (0.0ms) SELECT * FROM users WHERE (users.id = 1)
Comment Load (0.8ms) SELECT * FROM comments WHERE
(comments.id = 14)
CACHE (0.0ms) SELECT * FROM comments WHERE (comments.id =
14)
Rendered comments/_comment (32.8ms)
CACHE (0.0ms) SELECT * FROM users WHERE (users.id = 1)
Comment Load (0.8ms) SELECT * FROM comments WHERE
(comments.id = 17)
Rendered comments/_comment (32.8ms)
Completed in 592ms (View: 371, DB: 19) | 200 OK [http://localhost/
submissions/4-university-offers-beatles-degree]

4

On Mar 11, 3:05 pm, cdubd [email protected] wrote:

database for each comment.

What’s in the _comment partial ?

Fred

5

What’s in the _comment partial ?

get ready… this is a pretty hairy partial… This was my first shot
at coming up with a javascript driven comment system. Any tips on
things to change would be appreciated.

<%div_for(comment) do %>

<%= user_for(comment.user_id)%><%= time_ago(comment.created_at)%>
<%= comment.body %>
<%if !logged_in? %>
    <div class="ranked"><span id="up">ranked!</span><span

id=“down”>ranked!


<%else%>

<% form_remote_for [comment, Vote.new], :html => {:id =>
“new_vote_up”} do |f| %>

<%= f.submit ‘up’, :id => ‘up’,:onmouseover
=>“this.className=‘hov_up’”, :onmouseout =>“this.className=’’” %>

<% end %>
<% form_remote_for [comment, Vote.new], :html => {:id =>
“new_vote_down”} do |f| %>

<%= f.submit ‘down’, :id => ‘down’,:onmouseover
=>“this.className=‘hov_down’”, :onmouseout =>“this.className=’’” %>

<% end %>


<%end%> 
<div id="vote_score_<%=comment.id%>" class="cvote_score">
  <%= comment.reload.total_votes %>
</div>
<%= comment_reply_tag(comment)%>
<% form_remote_for [@submission, Comment.new], :html => {:id => "reply_field_#{comment.id}"} do |f| %> <%= hidden_field_tag "comment_id", "#{comment.id}"%> <%= hidden_field_tag "status", "reply"%> <%= f.text_area :body%> <%= f.submit "Submit"%> <% end %> 
</div>
<div class="clear"></div>
<div id="reply_left_<%=comment.id%>" >

  <%= link_to_function "reply", :id => "reply_link" do |page|
    page.show("reply_form_#{comment.id}")
  end%>
</div>
<%end %>
6

On Mar 11, 3:31 pm, cdubd [email protected] wrote:

What’s in the _comment partial ?

get ready… this is a pretty hairy partial… This was my first shot
at coming up with a javascript driven comment system. Any tips on
things to change would be appreciated.

<%div_for(comment) do %>

<%= user_for(comment.user_id)%><div

I don’t know that user_for does, but that obviously won’t use the
comment.user object that has already been loaded.

            <div id="vote_score_<%=comment.id%>" class="cvote_score">
                    <%= comment.reload.total_votes %>

Well this is why you get lots of queries loading the comment

Fred

7

another quick update. For my submission model, which is a similar
setup to the comment model I have:

user has_many :submissions, submission belongs_to :user, and with this
relationship I am able to call:

submission.user.username

but this doesn’t work for comments for some reason… I cannot call
comment.user.username

8

<%div_for(comment) do %>

<%= user_for(comment.user_id)%><div

I don’t know that user_for does, but that obviously won’t use the
comment.user object that has already been loaded.

well, the User object has many comments, the comments belong to user.
As far as I can tell, calling comment.user will return nothing because
there is only a user_id field.
perhaps I could set up a Comment has_one :user relationship?

anyway the user_for method just returns the user name that belongs to
that particular user_id.

     <div id="vote_score_<%=comment.id%>" class="cvote_score">

                    <%= comment.reload.total_votes %>

Well this is why you get lots of queries loading the comment

Thanks, I can’t remember why that reload was ever put in there, but it
certainly removed all the database calls when I got rid of it.

9

ActionView::TemplateError (You have a nil object when you didn’t
expect it!
You might have expected an instance of Array.
The error occurred while evaluating nil.include?) on line #2 of app/
views/comments/_comment.html.erb:
1: <%div_for(comment) do %>
2:

<%= comment.user.username %><%= time_ago(comment.created_at)%>

3:
<%= comment.body %>

4:

I have tested it in the console and you are right… I can call
comment.user there. I am getting this error however when I try and do
it the view.

10

On Mar 11, 4:22 pm, cdubd [email protected] wrote:

<%div_for(comment) do %>

<%= user_for(comment.user_id)%><div

I don’t know that user_for does, but that obviously won’t use the
comment.user object that has already been loaded.

well, the User object has many comments, the comments belong to user.
As far as I can tell, calling comment.user will return nothing because
there is only a user_id field.
perhaps I could set up a Comment has_one :user relationship?

I’m not sure I follow. if comment belongs_to user, then comment.user
will return the user with id comment.user_id.

Fred

11

On Mar 11, 4:37 pm, cdubd [email protected] wrote:

I have tested it in the console and you are right… I can call
comment.user there. I am getting this error however when I try and do
it the view.

There’s probably a comment with no user somewhere.

Fred

12

I am running edge rails so this could very well be the problem. I was
actually getting a similar error message before when I was writing the
code for the create method on the comments controller. I scratched my
head, but couldn’t figure it out, then suddenly it stopped. I will
poke around with it, but the error is a tricky one.

Thanks for all your help fred, I think you are the only person that
responds to my requests on here.

13

just in case you are interested. I ran the debugger to check things
out, and the comment object does have a valid user_id, but when it
tries to call comment.user.username it tells me it is trying to
evaluate nill.include?

do you think this can be an issue with rails 2.3.1?

very mysterious indeed. I will be quite sad if it turns out to be
something silly.