logo标签列表

Eager Loading，模型方法，fields_for，each和N+1 （注：这是提问标题，不需要回答）

ruby-on-railsrubyruby-on-rails-4
3

我有一个相当复杂的应用程序（超过30个表），存在一些持久的N+1问题，我认为这是因为我没有按照“Rails Way™”的方式做某些事情。

下面我将给出一个更加复杂的例子。这个例子涉及到四个表：clins、positions_tasks、tasks和labor_hours。

positions_tasks是一个三方多对多关联，连接了clins、positions(在这个例子中不需要)，以及tasks，并且它has_many :labor_hours。labor_hours表每年都有一个整数字段以及其他数据。一个名为total_hours的方法会将所有月份的时间总和计算出来，作为该年的总工时。在clin视图中，它会显示clin信息以及与其相关的所有任务[以及其他相关数据]的表格，然后汇总每个任务的工时，其中has_many :labor_hours, :through => :positions_tasks。我使用eager load加载所有相关的表格，包括labor_hours，所有的N+1问题都解决了，除了labor_hours。

以下是代码片段。

clins_controller中的eager load：

@clin = Clin.includes(:proposal).includes(:positions_tasks).includes(:tasks).includes(:labor_hours).includes(:wbss).find(params[:id])`

在clins/_form.html.erb中显示表格行：
    <tbody>
        <% @clin.tasks.distinct.each do |t| %>
          <%= f.fields_for :task, t do |builder| %>
            <%= render "tasks/task_row", f: builder %>
          <% end %>
        <% end %>
    </tbody>

_task_row 部分：

<tr>
    <td><%= f.object.wbs_line_item.wbs.wbs_title %></td>
    <td><%= f.object.wbs_line_item.wbs_line_item %></td>
    <td><%= f.object.description %></td>
    <td><%= f.object.labor_hours.distinct.each.sum(&:total_hours) %>
    <td><div id="jump">
      <%= link_to "Edit", {:controller => :tasks, :action => :edit, :id => f.object.id } %>
    </div></td>
</tr>

Clin 模型：

class Clin < ActiveRecord::Base
  nilify_blanks

  belongs_to :proposal

  belongs_to :parent, :class_name => "Clin"
  has_many :children, :class_name => "Clin"

  has_many :positions_tasks
  has_many :labor_hours, :through => :positions_tasks
  has_many :tasks, :through => :positions_tasks
  has_many :wbs_line_items, :through => :tasks
  has_many :wbss, :through => :wbs_line_items
  has_many :pws_line_items, :through => :wbs_line_items
  has_many :pwss, :through => :wbss
end

人工时模型:

class LaborHours < ActiveRecord::Base
  nilify_blanks

  belongs_to :positions_task
  belongs_to :year

  has_one :proposal, :through => :positions_task
  has_many :valid_years, :through => :proposal, :source => :years

  def total_hours
    m1 + m2 + m3 + m4 + m5 + m6 + m7 + m8 + m9 + m10 + m11 + m12
  end
end

职位任务模型：

class PositionsTask < ActiveRecord::Base
  nilify_blanks

  belongs_to :task
  belongs_to :position
  belongs_to :clin

  has_many :labor_hours

  has_one :company, :through => :position
  has_one :proposal, :through => :clin
  has_one :wbs_line_item, :through => :task
  delegate :wbs, :to => :wbs_line_item

  delegate :pws_line_items, :to => :wbs_line_item
  delegate :pwss, :to => :wbs_line_item

  validates_presence_of :task
  validates_presence_of :position
  validates_presence_of :clin

  accepts_nested_attributes_for :labor_hours, allow_destroy: true
end

任务模型：
class Task < ActiveRecord::Base
  nilify_blanks

  belongs_to :wbs_line_item
  belongs_to :task_category

  has_many :positions_tasks

  has_many :labor_hours, :through => :positions_tasks
  has_many :positions, :through => :positions_tasks
  has_many :clins, :through => :positions_tasks
  has_many :proposals, :through => :positions_tasks

  delegate :wbs, :to => :wbs_line_item
  delegate :pws_line_items, :to => :wbs_line_item
  delegate :pwss, :to => :wbs

  accepts_nested_attributes_for :positions_tasks, allow_destroy: true
  accepts_nested_attributes_for :labor_hours, allow_destroy: true

  validates_associated :positions_tasks

end

GET 和 SQL 加载：

Started GET "/clins/11/edit" for 127.0.0.1 at 2015-07-20 17:48:49 -0400
Processing by ClinsController#edit as HTML
  Parameters: {"id"=>"11"}
  Clin Load (0.2ms)  SELECT  "clins".* FROM "clins" WHERE "clins"."id" = $1 LIMIT 1  [["id", 11]]
  Proposal Load (0.2ms)  SELECT "proposals".* FROM "proposals" WHERE "proposals"."id" IN (1)
  PositionsTask Load (0.4ms)  SELECT "positions_tasks".* FROM "positions_tasks" WHERE "positions_tasks"."clin_id" IN (11)
  Task Load (0.6ms)  SELECT "tasks".* FROM "tasks" WHERE "tasks"."id" IN (1, 2, 3, 5, 6, 7, 8, 9, 10, 11, 12, 14, 15, 16, 17, 18, 20, 23, 24)
  LaborHours Load (1.1ms)  SELECT "labor_hours".* FROM "labor_hours" WHERE "labor_hours"."positions_task_id" IN (1, 2, 3, 6, 7, 8, 9, 10, 12, 13, 14, 18, 19, 20, 21, 23, 24, 25, 26, 27, 30, 35, 36, 37)
  WbsLineItem Load (0.5ms)  SELECT "wbs_line_items".* FROM "wbs_line_items" WHERE "wbs_line_items"."id" IN (310, 312, 314, 316, 317, 318, 319, 413, 320, 321, 322, 324, 325, 326, 327, 328, 330, 333, 334)
  Wbs Load (0.4ms)  SELECT "wbss".* FROM "wbss" WHERE "wbss"."id" IN (1)
  Clin Load (0.2ms)  SELECT "clins".* FROM "clins"
  Rendered tasks/_task_header.html.erb (0.0ms)
  LaborHours Load (0.4ms)  SELECT "labor_hours".* FROM "labor_hours" INNER JOIN "positions_tasks" ON "labor_hours"."positions_task_id" = "positions_tasks"."id" WHERE "positions_tasks"."task_id" = $1  [["task_id", 1]]
  Rendered tasks/_task_row.erb (2.6ms)
  LaborHours Load (0.3ms)  SELECT "labor_hours".* FROM "labor_hours" INNER JOIN "positions_tasks" ON "labor_hours"."positions_task_id" = "positions_tasks"."id" WHERE "positions_tasks"."task_id" = $1  [["task_id", 2]]
  Rendered tasks/_task_row.erb (1.7ms)
  LaborHours Load (0.2ms)  SELECT "labor_hours".* FROM "labor_hours" INNER JOIN "positions_tasks" ON "labor_hours"."positions_task_id" = "positions_tasks"."id" WHERE "positions_tasks"."task_id" = $1  [["task_id", 3]]
  Rendered tasks/_task_row.erb (1.4ms)
  LaborHours Load (0.2ms)  SELECT "labor_hours".* FROM "labor_hours" INNER JOIN "positions_tasks" ON "labor_hours"."positions_task_id" = "positions_tasks"."id" WHERE "positions_tasks"."task_id" = $1  [["task_id", 5]]
  Rendered tasks/_task_row.erb (1.3ms)
  LaborHours Load (0.2ms)  SELECT "labor_hours".* FROM "labor_hours" INNER JOIN "positions_tasks" ON "labor_hours"."positions_task_id" = "positions_tasks"."id" WHERE "positions_tasks"."task_id" = $1  [["task_id", 6]]
  Rendered tasks/_task_row.erb (1.4ms)
  LaborHours Load (0.2ms)  SELECT "labor_hours".* FROM "labor_hours" INNER JOIN "positions_tasks" ON "labor_hours"."positions_task_id" = "positions_tasks"."id" WHERE "positions_tasks"."task_id" = $1  [["task_id", 7]]
  Rendered tasks/_task_row.erb (1.5ms)
  LaborHours Load (0.2ms)  SELECT "labor_hours".* FROM "labor_hours" INNER JOIN "positions_tasks" ON "labor_hours"."positions_task_id" = "positions_tasks"."id" WHERE "positions_tasks"."task_id" = $1  [["task_id", 8]]
  Rendered tasks/_task_row.erb (1.3ms)
  LaborHours Load (0.2ms)  SELECT "labor_hours".* FROM "labor_hours" INNER JOIN "positions_tasks" ON "labor_hours"."positions_task_id" = "positions_tasks"."id" WHERE "positions_tasks"."task_id" = $1  [["task_id", 9]]
  Rendered tasks/_task_row.erb (1.3ms)
  LaborHours Load (0.4ms)  SELECT "labor_hours".* FROM "labor_hours" INNER JOIN "positions_tasks" ON "labor_hours"."positions_task_id" = "positions_tasks"."id" WHERE "positions_tasks"."task_id" = $1  [["task_id", 10]]
  Rendered tasks/_task_row.erb (1.9ms)
  LaborHours Load (0.2ms)  SELECT "labor_hours".* FROM "labor_hours" INNER JOIN "positions_tasks" ON "labor_hours"."positions_task_id" = "positions_tasks"."id" WHERE "positions_tasks"."task_id" = $1  [["task_id", 11]]
  Rendered tasks/_task_row.erb (1.5ms)
  LaborHours Load (0.4ms)  SELECT "labor_hours".* FROM "labor_hours" INNER JOIN "positions_tasks" ON "labor_hours"."positions_task_id" = "positions_tasks"."id" WHERE "positions_tasks"."task_id" = $1  [["task_id", 12]]
  Rendered tasks/_task_row.erb (2.2ms)
  LaborHours Load (0.5ms)  SELECT "labor_hours".* FROM "labor_hours" INNER JOIN "positions_tasks" ON "labor_hours"."positions_task_id" = "positions_tasks"."id" WHERE "positions_tasks"."task_id" = $1  [["task_id", 14]]
  Rendered tasks/_task_row.erb (2.6ms)
  LaborHours Load (0.4ms)  SELECT "labor_hours".* FROM "labor_hours" INNER JOIN "positions_tasks" ON "labor_hours"."positions_task_id" = "positions_tasks"."id" WHERE "positions_tasks"."task_id" = $1  [["task_id", 15]]
  Rendered tasks/_task_row.erb (2.2ms)
  LaborHours Load (0.2ms)  SELECT "labor_hours".* FROM "labor_hours" INNER JOIN "positions_tasks" ON "labor_hours"."positions_task_id" = "positions_tasks"."id" WHERE "positions_tasks"."task_id" = $1  [["task_id", 16]]
  Rendered tasks/_task_row.erb (1.5ms)
  LaborHours Load (0.3ms)  SELECT "labor_hours".* FROM "labor_hours" INNER JOIN "positions_tasks" ON "labor_hours"."positions_task_id" = "positions_tasks"."id" WHERE "positions_tasks"."task_id" = $1  [["task_id", 17]]
  Rendered tasks/_task_row.erb (1.9ms)
  LaborHours Load (0.3ms)  SELECT "labor_hours".* FROM "labor_hours" INNER JOIN "positions_tasks" ON "labor_hours"."positions_task_id" = "positions_tasks"."id" WHERE "positions_tasks"."task_id" = $1  [["task_id", 18]]
  Rendered tasks/_task_row.erb (1.6ms)
  LaborHours Load (0.3ms)  SELECT "labor_hours".* FROM "labor_hours" INNER JOIN "positions_tasks" ON "labor_hours"."positions_task_id" = "positions_tasks"."id" WHERE "positions_tasks"."task_id" = $1  [["task_id", 20]]
  Rendered tasks/_task_row.erb (1.9ms)
  LaborHours Load (0.2ms)  SELECT "labor_hours".* FROM "labor_hours" INNER JOIN "positions_tasks" ON "labor_hours"."positions_task_id" = "positions_tasks"."id" WHERE "positions_tasks"."task_id" = $1  [["task_id", 23]]
  Rendered tasks/_task_row.erb (1.6ms)
  LaborHours Load (0.3ms)  SELECT "labor_hours".* FROM "labor_hours" INNER JOIN "positions_tasks" ON "labor_hours"."positions_task_id" = "positions_tasks"."id" WHERE "positions_tasks"."task_id" = $1  [["task_id", 24]]
  Rendered tasks/_task_row.erb (1.9ms)
  Rendered clins/_form.html.erb (47.6ms)
  Rendered clins/_errors.html.erb (0.0ms)
  Rendered clins/edit.html.erb within layouts/application (48.6ms)
  Rendered layouts/_header.html.erb (60.5ms)
  Rendered layouts/_sidenav.html.erb (0.4ms)
  Rendered layouts/_footer.html.erb (0.0ms)
Completed 200 OK in 140ms (Views: 106.4ms | ActiveRecord: 8.8ms)

我认为发生的情况是，在_form.html.erb中的distinct.each和fields_for中，急切加载丢失了，因为它传递的是任务对象而不是诊所对象，或者total_hours的调用会导致每个调用对象都要进行一次加载，但我不确定该如何确定哪一个是问题，也不知道如何解决这些问题。
如何在表格中为每个任务提供总和为task.labor_hours.total_hours的值，而不会出现N+1加载的labor_hours？
专业提示（并不能解决你的问题）：你可以向includes传递多个参数：Clin.includes(:proposal, :positions_tasks, :tasks, :labor_hours, :wbss).find(params[:id]) - Jordan Running
从现在开始，我将使用那个约定。谢谢！ - Joseph Freivald
1个回答
1
我不确定，但我有一个理论。你有这个（我已经删除了现在与我们无关的includes调用）：
@clin = Clin.includes(:tasks).includes(:labor_hours).find(params[:id])

你在这里所做的是急切加载与每个Clin相关联的Tasks和与每个Clin相关联的LaborHours - 到目前为止，一切都好，但在你的视图中，你正在这样做（或多或少）：
@clin.tasks.distinct.each do |task|
  # inside the partial...
  task.labor_hours...
end

在这里，您没有访问与每个Clin相关联的LaborHours（这是您急切加载的内容），而是访问与每个Clin相关联的每个Task关联的LaborHours。要访问与每个Clin相关联的LaborHours，您需要执行以下操作：
@clin.labor_hours.each do |labor_hour|
  # ...
end

但是，由于您正在呈现任务（而不仅仅是劳务小时），我认为这不是您想要的。相反，您需要告诉Rails您想要急切地加载二级关联 - 即与任务相关联的LaborHours，而不是与Clins相关联的LaborHours - 通过向includes传递哈希：

@clin = Clin.includes(:tasks => :labor_hours).find(params[:id])

附注：你还可以进行一些额外的改进，例如，看起来你实际上没有使用LaborHours中的任何属性，你实际上只是使用了total_hours列的总和。但是在Ruby中计算总和是浪费的，当你可以让数据库来完成它。然而，这超出了本答案的范围。

你太棒了。我更新了控制器为：@clin = Clin.includes(:proposal, :positions_tasks, :wbss).includes(:tasks => :labor_hours).find(params[:id])，现在N+1问题已经解决了。 - Joseph Freivald
太好了！您还可以执行Clin.includes(:proposal, :positions_tasks, :wbss, :tasks => :labor_hours).find(params[:id])。 - Jordan Running
我尝试了Clin.includes(:proposal, :positions_tasks, :tasks => :labor_hours, :wbss).find(params[:id])，但NetBeans不喜欢它，所以我用了另一种方法，最终它可以正常工作，就像你输入的那样。顺便问一下，在模型/迁移中我需要放什么来让数据库为我执行加法操作？ - Joseph Freivald
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接