Forum: Ruby on Rails eager loading is letting me down

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.
Taylor S. (Guest)
on 2009-01-02 22:52
Class User has_many Referrals.  Referrals have an instance variable
'point_value.'  So on my users/index action I want to list the user
info, total referrals, and then total points.  However, I am getting the
notorious N+1 problem when running this action.

USERS CONTROLLER
  def index
    @users = User.find(:all, :include => :referrals)
  end

INDEX VIEW
<% for user in @users %>
  <tr>
    <td><%= h user.email %></td>
    <td>Referrals: <%= user.referrals.size %></td>
    <td>Points: <%= user.total_points %>

USER.RB MODEL
    has_many  :referrals,
            :foreign_key => "referer_id"

  def total_points
    self.referrals.sum :point_value
  end

The index action nails me with a nasty N+2 queries (i have six user
records in the test db):

Processing UsersController#index (for 127.0.0.1 at 2009-01-02 15:49:16)
[GET]
  User Load (4.3ms)   SELECT * FROM "users"
  Referral Load (1.4ms)   SELECT "referrals".* FROM "referrals" WHERE
("referrals".referer_id IN (1,2,3,4,6,7))
Rendering template within layouts/users
Rendering users/index
  SQL (0.3ms)   SELECT sum("referrals".point_value) AS sum_point_value
FROM "referrals" WHERE ("referrals".referer_id = 1)
  SQL (0.3ms)   SELECT sum("referrals".point_value) AS sum_point_value
FROM "referrals" WHERE ("referrals".referer_id = 2)
  SQL (0.3ms)   SELECT sum("referrals".point_value) AS sum_point_value
FROM "referrals" WHERE ("referrals".referer_id = 3)
  SQL (0.2ms)   SELECT sum("referrals".point_value) AS sum_point_value
FROM "referrals" WHERE ("referrals".referer_id = 4)
  SQL (0.3ms)   SELECT sum("referrals".point_value) AS sum_point_value
FROM "referrals" WHERE ("referrals".referer_id = 6)
  SQL (0.3ms)   SELECT sum("referrals".point_value) AS sum_point_value
FROM "referrals" WHERE ("referrals".referer_id = 7)

Is this a failing of the rails .sum method to look inside the collection
or am I doing something wrong?
Taylor S. (Guest)
on 2009-01-02 23:07
Fixed.  The short version of the story is that rails' .sum helper hits
the DB N+1 times.  Writing a custom sum method solves this problem.  In
my case, the top method only hits the DB twice whereas the second method
hits the DB N+2 times.

  # 2 queries
  def total_points
    sum = 0
    self.referrals.each {|ref| sum += ref.point_value}
    sum
  end

  # N+2 queries
  def total_points
    self.referrals.sum :point_value
  end

So :include DOES work as long as you are careful to manipulate the
collection in ruby and not use another ActiveRecord method to do
summation.
Rob B. (Guest)
on 2009-01-02 23:38
(Received via mailing list)
You probably could have said:

  def total_points
    self.referrals.to_a.sum(&:point_value)
  end

And then #sum is from Enumerable (as extended by ActiveSupport) rather
than ActiveRecord::Calculations::ClassMethods (note the use of .to_a
in there to get a real array rather than an association proxy)

-Rob

On Jan 2, 2009, at 4:07 PM, Taylor S. wrote:

>    sum = 0
> collection in ruby and not use another ActiveRecord method to do
> summation.
> --
> Posted via http://www.ruby-forum.com/.
>
> >

Rob B.    http://agileconsultingllc.com
removed_email_address@domain.invalid
+1 513-295-4739
Skype:  rob.biedenharn
This topic is locked and can not be replied to.