Eager Loading with "has many through" -- do I need Arel?

I have three tables: users, members, projects. The middle is a join table expressing a has-many-through between the other two tables; and it has some attributes of interest, including join_code and activated.

More expansively:

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 :use开发者_开发知识库rs, :through => :members
end

Goal: Given a particular user, I want a query that will get all the projects, and eager load only the member records that link those projects to the user.

So far I have this method in user.rb that does a query:

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

But it's not enough. I'd like to then be able to do this in the view code:

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

Here, normally, I'd have project.members, but in my context I'm only interested in that one member record that links back to the user.

Here is what I think the raw SQL should look like

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';

How to do that in Arel (or ActiveRecord)?

---

I may have something of an answer here, namely that the ActiveRecord code I wrote seems pretty reasonable. Again, here's that query:

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

On a run through the UI with sample data it generates this output from Rails server:

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

Then later in the view code I can do this:

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

That expression to get the member record is pretty ugly in the view, but select is an Array method, not a query, and no extra DB hits other than the two shown above appear in the output from Rails server. Thus I guess my n+1 problem is solved.

---

Add an association called live_members on the Project class.

class Project < ActiveRecord::Base
  has_many :live_members, :class_name => "Member", 
                :conditions => {:join_code => nil, :activated => true}
  has_many :members
  has_many :users, :through => :members
end

Add an association called live_projects on the User class.

class User < ActiveRecord::Base
  has_many :members
  has_many :projects, :through => :members
  has_many :live_projects, :through => :members, :source => :project, 
             :include => :live_member, :order => "projects.name"
end

Now you can:

user.live_projects

---

It seems that you expect there to be at most one active member linking each user to a project. If this is the case the following should work:

In member.rb:

scope :live, where(:join_code => nil, :activated => true)

In user.rb:

def live_projects_with_members
  members.live.includes(:project).group_by(&:project)
end

In your view:

<% current_user.live_projects_with_members.each do |project, members| %>
  <% member = members.first %>
  <li project_id="<%= project.id %>" class="<%= 'active' if project == @project %>">
      (Do something with that member record here)
  </li>
<% end %>

If you then want to add an extra join for your usage stats you can do this:

def live_projects_with_members
  members.live.includes(:project, :stats).group_by(&:project)
end