Reducing database hits in Rails

I have two models, projects and words where project has_many :words (words is really just a model that holds the quantity of words written each day for each project.

I have a view that I build like this, which shows all the days from start to end in the project and how many, if any words were written on that day:

<% project_range(@project.start, @project.end).each do |day| %>
    <%= day %>
    <%= get_word_count_by_date(@project,开发者_开发百科 day ) %>
<% end %>

And in my helper:

def project_range(start, finish)
  project_days = (start..finish).collect
end

def get_word_count_by_date(project, date)
  word_count = Word.find_by_project_id_and_wrote_on(project, date)
  if word_count
    word_count.quantity
  else
    0
  end
end

Trouble is, in the view, that hits my database a lot. For example, if the project is 30 days, I get:

  Word Load (0.2ms)  SELECT "words".* FROM "words" WHERE "words"."project_id" = 2 AND "words"."wrote_on" = '2011-09-01' LIMIT 1
  Word Load (0.1ms)  SELECT "words".* FROM "words" WHERE "words"."project_id" = 2 AND "words"."wrote_on" = '2011-09-02' LIMIT 1
  Word Load (0.1ms)  SELECT "words".* FROM "words" WHERE "words"."project_id" = 2 AND "words"."wrote_on" = '2011-09-03' LIMIT 1
  Word Load (0.1ms)  SELECT "words".* FROM "words" WHERE "words"."project_id" = 2 AND "words"."wrote_on" = '2011-09-04' LIMIT 1
  Word Load (0.1ms)  SELECT "words".* FROM "words" WHERE "words"."project_id" = 2 AND "words"."wrote_on" = '2011-09-05' LIMIT 1
  Word Load (0.1ms)  SELECT "words".* FROM "words" WHERE "words"."project_id" = 2 AND "words"."wrote_on" = '2011-09-06' LIMIT 1
  Word Load (0.1ms)  SELECT "words".* FROM "words" WHERE "words"."project_id" = 2 AND "words"."wrote_on" = '2011-09-07' LIMIT 1
  Word Load (0.1ms)  SELECT "words".* FROM "words" WHERE "words"."project_id" = 2 AND "words"."wrote_on" = '2011-09-08' LIMIT 1
  Word Load (0.1ms)  SELECT "words".* FROM "words" WHERE "words"."project_id" = 2 AND "words"."wrote_on" = '2011-09-09' LIMIT 1
  Word Load (0.1ms)  SELECT "words".* FROM "words" WHERE "words"."project_id" = 2 AND "words"."wrote_on" = '2011-09-10' LIMIT 1
  Word Load (0.1ms)  SELECT "words".* FROM "words" WHERE "words"."project_id" = 2 AND "words"."wrote_on" = '2011-09-11' LIMIT 1
  Word Load (0.1ms)  SELECT "words".* FROM "words" WHERE "words"."project_id" = 2 AND "words"."wrote_on" = '2011-09-12' LIMIT 1
  Word Load (0.1ms)  SELECT "words".* FROM "words" WHERE "words"."project_id" = 2 AND "words"."wrote_on" = '2011-09-13' LIMIT 1
  Word Load (0.1ms)  SELECT "words".* FROM "words" WHERE "words"."project_id" = 2 AND "words"."wrote_on" = '2011-09-14' LIMIT 1
  Word Load (0.1ms)  SELECT "words".* FROM "words" WHERE "words"."project_id" = 2 AND "words"."wrote_on" = '2011-09-15' LIMIT 1
  Word Load (0.1ms)  SELECT "words".* FROM "words" WHERE "words"."project_id" = 2 AND "words"."wrote_on" = '2011-09-16' LIMIT 1
  Word Load (0.1ms)  SELECT "words".* FROM "words" WHERE "words"."project_id" = 2 AND "words"."wrote_on" = '2011-09-17' LIMIT 1
  Word Load (0.1ms)  SELECT "words".* FROM "words" WHERE "words"."project_id" = 2 AND "words"."wrote_on" = '2011-09-18' LIMIT 1
  Word Load (0.1ms)  SELECT "words".* FROM "words" WHERE "words"."project_id" = 2 AND "words"."wrote_on" = '2011-09-19' LIMIT 1
  Word Load (0.1ms)  SELECT "words".* FROM "words" WHERE "words"."project_id" = 2 AND "words"."wrote_on" = '2011-09-20' LIMIT 1
  Word Load (0.1ms)  SELECT "words".* FROM "words" WHERE "words"."project_id" = 2 AND "words"."wrote_on" = '2011-09-21' LIMIT 1
  Word Load (0.1ms)  SELECT "words".* FROM "words" WHERE "words"."project_id" = 2 AND "words"."wrote_on" = '2011-09-22' LIMIT 1
  Word Load (0.1ms)  SELECT "words".* FROM "words" WHERE "words"."project_id" = 2 AND "words"."wrote_on" = '2011-09-23' LIMIT 1
  Word Load (0.1ms)  SELECT "words".* FROM "words" WHERE "words"."project_id" = 2 AND "words"."wrote_on" = '2011-09-24' LIMIT 1
  Word Load (0.1ms)  SELECT "words".* FROM "words" WHERE "words"."project_id" = 2 AND "words"."wrote_on" = '2011-09-25' LIMIT 1
  Word Load (0.1ms)  SELECT "words".* FROM "words" WHERE "words"."project_id" = 2 AND "words"."wrote_on" = '2011-09-26' LIMIT 1
  Word Load (0.1ms)  SELECT "words".* FROM "words" WHERE "words"."project_id" = 2 AND "words"."wrote_on" = '2011-09-27' LIMIT 1
  Word Load (0.1ms)  SELECT "words".* FROM "words" WHERE "words"."project_id" = 2 AND "words"."wrote_on" = '2011-09-28' LIMIT 1
  Word Load (0.1ms)  SELECT "words".* FROM "words" WHERE "words"."project_id" = 2 AND "words"."wrote_on" = '2011-09-29' LIMIT 1
  Word Load (0.1ms)  SELECT "words".* FROM "words" WHERE "words"."project_id" = 2 AND "words"."wrote_on" = '2011-09-30' LIMIT 1

Is there a way to do this without querying every single day in the length of the project? I tried starting by loading all of a project's words first, but couldn't figure out how to get the days with zero in there.

This is a "n+1" problem... What you want to do is join words and projects in your query so that all the words for each project are included in the result set.

Assuming that your project "has_many :words":

@project = Project.find(:id, :include => :words)

Now the words collection on each project will be pre-populated with the words in just 1 query.

Read more under the "Eager Loading of Associations" http://api.rubyonrails.org/classes/ActiveRecord/Associations/ClassMethods.html

You could use a block helper to keep it clean and avoid looking them up:

def project_range(project, start, finish, &blk)
  words = project.words.where(:wrote_on => start..finish)
  word_map = words.index_by(&:wrote_on)
  for day in start..finish
    word_count = word_map[day] ? word_map[day].quantity : 0
    blk.call(day, word_count)
  end
end

Then use it like

<% project_range(project, start, finish) do |day, word_count| %>
   <%= day %>
   <%= word_count %>
<% end %>

You could also clean up the helper a bit (avoid having SQL in it), maybe by passing the list of pre-fetched words or using a scope

EDIT: m_x suggested the start..finish where clause on wrote_on which is cleaner!

I'd go for something like:

@words = @project.words.where("wrote_on >= ? and wrote_on <= ?", start, end)

and than use group_by to display them in the view:

@words.group_by(&:wrote_on).each do |day, word|
<%= day %>
<%= word.quantity %>
end