How to do join with SUM in ActiveRecord

Question

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

Answer 1

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.