How to do join with SUM in ActiveRecord

activerecord, aggregate, database, join, ruby-on-rails

Solution

If this gets computed frequently, you're probably better off keeping a summary statistic in the project record (total_votes). You can either update on each vote, or update via a cron job.

I think you're looking for the following though:

@totals = Vote.sum(:value,:group=>:project_id)
@projects = Project.find(:all)

then `

<%=h @project.title %> has <%= @totals[@project.id] %> votes.

`

Problem

Let's say I have two simple models ``` project t.string :title vote t.references :project t.integer :value ``` When I do loop throuh all projects, I also want to include sum of all votes, because doing ``` projects = Project.all foreach project in projects sum = project.votes.sum(:value) ... ``` isn't really effective. Is there any way how to do this without manualy writing the SQL? Something like ``` SELECT p.*, SUM(v.value) FROM projects p LEFT JOIN votes v ON v.project_id = p.id GROUP BY p.id ```

Original source