Eager loading comments associated with user submissions

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!

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

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]

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

database for each comment.

What’s in the _comment partial ?

Fred

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 %>

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

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

<%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.

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.

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

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

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.

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.

This forum is not affiliated to the Ruby language, Ruby on Rails framework, nor any Ruby applications discussed here.

| Privacy Policy | Terms of Service | Remote Ruby Jobs