Let us say I've two simple models

project
   t.string :title

vote
   t.references :project
   t.integer :value

After I do loop throuh all projects, I should also include amount of all votes, because doing

projects = Project.all

foreach project in projects
    sum = project.votes.sum(:value)
    ...

is not really effective.

Can there be in whatever way how to get this done without manualy writing the SQL? Something similar to

SELECT p.*, SUM(v.value)
FROM projects p
LEFT JOIN votes v
ON v.project_id = p.id
GROUP BY p.id

If the will get calculated frequently, you are most likely best keeping an overview statistic within the project record (total_votes). You may either update on each election, or update using a cron job.

I believe you are searching for the next though:


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

then

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

This will get it done:

Project.all(
  :select => 'projects.*, sum(votes.value) as sum_votes',
  :joins => 'left outer join votes on votes.project_id = projects.id',
  :group => 'projects.id'
)

You may also allow it to be the default scope in your Project model so most commonly it is available:

default_scope :select => 'projects.*, sum(votes.value) as sum_votes',
  :joins => 'left outer join votes on votes.project_id = projects.id',
  :group => 'projects.id'