ruby-on-rails - Eager Loading with "has many through"—— 我需要 Arel 吗？

Question

我有三张表:users , members , projects .中间是一个连接表，表示其他两个表之间的多 channel ；它有一些有趣的属性，包括 join_code和 activated .

更广泛地:

class User < ActiveRecord::Base
  has_many :members
  has_many :projects, :through => :members
end

class Member < ActiveRecord::Base
  belongs_to :user
  belongs_to :project
  # has a column called join_code
  # has a column called activated
  # Note that this class can be thought of as "membership"
end

class Project < ActiveRecord::Base
  has_many :members
  has_many :users, :through => :members
end

目标 :给定一个特定用户，我想要一个查询来获取所有项目，并急切加载 只有将这些项目链接到用户的成员记录。

到目前为止，我在 user.rb 中有这个方法做一个查询:

def live_projects
  self.projects.order("projects.name").includes(:members).where(:members => {:join_code => nil, :activated => true})
end

但这还不够。然后我希望能够在 View 代码中执行此操作:

<% current_user.live_projects.each do |project| %>
  <li project_id="<%= project.id %>">
    <% member = project.member %>
      (Do something with that member record here)
      <%= project.name %>
    <% end %>
  </li>
<% end %>

在这里，通常，我会有 project.members ，但在我的上下文中，我只对 感兴趣一 链接回用户的成员记录。

这是我认为原始 SQL 的样子

select projects.*, members.* 
from projects inner join members on projects.id = members.project_id
where members.user_id = X and members.join_code is null and members.activated = 't';

如何在 Arel(或 ActiveRecord)中做到这一点？

Answer 1

我在这里可能有一些答案，即 ActiveRecord我写的代码看起来很合理。再次，这是该查询:

def live_projects
  self.projects.order("projects.name").includes(:members).where(:members => {:join_code => nil, :activated => true})
end

在使用示例数据运行 UI 时，它会从 Rails 服务器生成以下输出:

Project Load (0.6ms)  SELECT "projects".* FROM "projects" INNER JOIN "members" ON "projects".id = "members".project_id WHERE "members"."join_code" IS NULL AND "members"."activated" = 't' AND (("members".user_id = 3)) ORDER BY projects.name
Member Load (2.0ms)  SELECT "members".* FROM "members" WHERE ("members".project_id IN (50,3,6,37,5,1))

然后在 View 代码中我可以这样做:

<% current_user.live_projects.each do |project| %>
  <li project_id="<%= project.id %>" class="<%= 'active' if project == @project %>">
    <% member = project.members.detect { |member| member.user_id == current_user.id } %>
    (Do something with that member record here)
  </li>
<% end %>

获取成员记录的表达式在 View 中非常难看，但是 select是 Array方法，而不是查询，并且除了上面显示的两个之外没有额外的 DB 命中出现在 Rails 服务器的输出中。因此我想我的 n+1 问题解决了。