Forum: Ruby on Rails Eager loading comments associated with user submissions.

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
cdubd (Guest)
on 2009-03-11 14:16
(Received via mailing list)
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
http://github.com/elight/acts_as_commentable_with_...

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!
Frederick C. (Guest)
on 2009-03-11 16:45
(Received via mailing list)
On Mar 11, 12:15 pm, cdubd <removed_email_address@domain.invalid> 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
cdubd (Guest)
on 2009-03-11 17:06
(Received via mailing list)
On Mar 11, 3:44 pm, Frederick C. <removed_email_address@domain.invalid>
wrote:
> On Mar 11, 12:15 pm, cdubd <removed_email_address@domain.invalid> 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]
Frederick C. (Guest)
on 2009-03-11 17:10
(Received via mailing list)
On Mar 11, 3:05 pm, cdubd <removed_email_address@domain.invalid> wrote:
> database for each comment.
>
What's in the _comment partial ?

Fred
cdubd (Guest)
on 2009-03-11 17:32
(Received via mailing list)
> 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 %>
  <div id="comment_user"><%= user_for(comment.user_id)%><div
class="time"><%= time_ago(comment.created_at)%></div></div>
  <div id="message"><%= comment.body %></div>
  <div class="cvote_area">


    <%if !logged_in? %>
        <div class="ranked"><span id="up">ranked!</span><span
id="down">ranked!</span></div>
      <%else%>
        <div id="vote_form_<%=comment.id%>" class="cvote_form">
           <% form_remote_for [comment, Vote.new], :html => {:id =>
"new_vote_up"} do |f| %>
             <div id="up">
               <%= f.submit 'up', :id => 'up',:onmouseover
=>"this.className='hov_up'", :onmouseout =>"this.className=''" %>
             </div>
           <% end %>
          <% form_remote_for [comment, Vote.new], :html => {:id =>
"new_vote_down"} do |f| %>
             <div id="down">
               <%= f.submit 'down', :id => 'down',:onmouseover
=>"this.className='hov_down'", :onmouseout =>"this.className=''" %>
             </div>
           <% end %>
            <div class ="clear"></div>
        </div>
    <%end%>

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

  </div>
  <div class="clear"></div>
  <div id="comment_footer_<%=comment.id%>" class="comment_footer">
    <%= comment_reply_tag(comment)%>
  </div>
  <div id="comment_replies_<%=comment.id%>" style="display:none">
    <div id="reply_form_<%=comment.id%>" class="reply_form" >
      <% 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>
  </div>
<%end %>
Frederick C. (Guest)
on 2009-03-11 18:10
(Received via mailing list)
On Mar 11, 3:31 pm, cdubd <removed_email_address@domain.invalid> 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 %>
>         <div id="comment_user"><%= 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
cdubd (Guest)
on 2009-03-11 18:22
(Received via mailing list)
> > <%div_for(comment) do %>
> >         <div id="comment_user"><%= 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.
cdubd (Guest)
on 2009-03-11 18:33
(Received via mailing list)
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
Frederick C. (Guest)
on 2009-03-11 18:34
(Received via mailing list)
On Mar 11, 4:22 pm, cdubd <removed_email_address@domain.invalid> wrote:
> > > <%div_for(comment) do %>
> > >         <div id="comment_user"><%= 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
cdubd (Guest)
on 2009-03-11 18:38
(Received via mailing list)
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:   <div id="comment_user"><%= comment.user.username %><div
class="time"><%= time_ago(comment.created_at)%></div></div>
3:   <div id="message"><%= comment.body %></div>
4:   <div class="cvote_area">


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.
Frederick C. (Guest)
on 2009-03-11 20:20
(Received via mailing list)
On Mar 11, 4:37 pm, cdubd <removed_email_address@domain.invalid> 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
cdubd (Guest)
on 2009-03-11 22:19
(Received via mailing list)
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.
cdubd (Guest)
on 2009-03-11 22:36
(Received via mailing list)
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 topic is locked and can not be replied to.