Mike Belyakov Mike Belyakov - 7 months ago 19
Ruby Question

How to group, sum and include in a single ActiveRecord query?

I have a

User
model, which
has_one
Picture
(
carrierwave
back-end) and
has_many
journal
s.

I need to get the top N users with their maximum score (the sum of the
score
field in the
Journal
model).

I wrote this scope

scope :top, lambda { |n=10|
joins(:picture, :journals)
.select("users.*, pictures.image, sum(journals.score) as total_score")
.order("total_score desc ")
.group("users.id", "pictures.image")
.limit(n)
}


It works, but, when I try to get the
Picture
with
User.top.picture
I get an extra request to the database.

If I
include(:picture)
in the
top
scope then the
total_score
field runs away.

How I can fix this query? I will be very pleased if you can advise about this topic.

Answer

I'd do this by outer joining users to a subquery for total score:

scope :top, lambda do |n=10|
  select("users.*, total_score")
    joins("left join (select user_id, sum(score) total_score from journals " +
      "group by user_id) total_scores on users.id = total_scores.user_id").
    order("total_score desc").
    limit(n).
    include(:picture)
end

It uses some manual SQL so is more database-specific, but since it returns one row per user it's easy to understand and extend.

Comments