减少 Rails 中的数据库命中 [英] Reducing database hits in Rails

问题描述

我有两个模型，projects 和 words，其中 project has_many :words(words 实际上只是一个保存单词数量的模型每天为每个项目编写.

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 %>

在我的助手中:

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

问题是，在视图中，它经常影响我的数据库.例如，如果项目是 30 天，我得到:

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.

推荐答案

您可以使用块助手来保持清洁并避免查找它们:

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

然后像这样使用

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

您也可以稍微清理一下帮助程序(避免在其中包含 SQL)，可能是通过传递预取单词列表或使用 scope

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

m_x 建议在 wrote_on 上使用 start..finish where 子句，它更干净！

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

这篇关于减少 Rails 中的数据库命中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！