Hey,
okay, you *can* use eager loading to reduce the number of hits to the
database (there's a 'but' coming at the end of this:
@projects = Project.find(:all, :conditions => ['your_conditions = ?',
:whatever],
:include => [:connections, :assignments])
@projects.each do |project|
# get the count of 'p' kind connections
project.connections.inject(0) {|m,v| m += 1 if v.kind == 'p'; m}
# get the count of 's' kind connections
project.connections.inject(0) {|m,v| m += 1 if v.kind == 'p'; m}
end
So... because you've loaded all the records into memory as regular
ruby objects you can traverse them and do whatever calculations/tests
you need, without hitting the database again.
BUT (told you it was coming) - this may actually be slower than the
way you're doing things now. If you have a massive query cache
(mysql) *and* these queries are executed repeatedly you will probably
find that eager loading with :include is *much* slower. You also need
to make sure you've correctly indexed all your join columns if you use
eager loading - otherwise it's virtually guaranteed to be slower (even
without a big query cache to skew the results).
You seem to be missing one bit of the puzzle which will reduce your
hundreds-of-queries to a small handful - namely grouping your counts.
Consider this:
@projects = Project.find(:all, :conditions => 'yadda') # NO :include HERE
@p_counts = Connection.count(:group => 'project_id',
:conditions => ['project_id in (?) and kind = ?',
@projects.collect(&:id), 'p'])
@s_counts = Connection.count(:group => 'project_id',
:conditions => ['project_id in (?) and kind = ?',
@projects.collect(&:id), 's'])
That's 3 queries, no eager loading, but everything you need to figure
out the counts for connections of kind p and s for the projects you've
loaded.
@p_counts and @s_counts are hashes keyed on project_id, where the
value is the counts. Note that if one of the projects has no p or s
connections it won't have an entry in the hash. As such you need to
use to_i to coerce any nil lookups to be 0. See below:
@projects.each do |project|
# any 'p' connections for this project?
@p_counts[project.id].to_i == 0
# any 's' connections for this project?
@s_counts[project.id].to_i == 0
end
Something to try out anyhow, and something that you can use to compare
against an eager loading solution. I'd be willing to bet a beer that
grouped counts will be faster than eager loading (at least 2 X as
fast).
Hope this helps,
Trevor
