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?
--
Posted via http://www.ruby-forum.com/.
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---
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.
--
Posted via http://www.ruby-forum.com/.
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---
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 Strait wrote:
>
> 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.
> --
> Posted via http://www.ruby-forum.com/.
>
> >
Rob Biedenharn http://agileconsultingllc.com
Rob-xa9cJyRlE0mWcWVYNo9pwxS2lgjeYSpx@public.gmane.org
+1 513-295-4739
Skype: rob.biedenharn
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---