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)
<%=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'