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

Question

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 :users, :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)?

One reason I'm interested to see an Arel solution is that there's a fourth table I have to get stats from that's linked to the members table, so I would like to know how to make this efficient. — Purplejacket, Sep 19 '11 at 22:02

Purplejacket · Answer 1 · 2011-09-19T16:06:06.477

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.

Replace `select` by `detect` to get rid of the array index get at the end. And why not add a method `member_with_id` to class `Project`, so you can do in the view code: `member = project.member_with_id(current_user.id)`? — mliebelt, Sep 17 '11 at 10:33
@mliebelt - thanks for the refinement with `detect` -- http://www.ruby-doc.org/core/classes/Enumerable.html#M001485 -- I edited the post to reflect that. Previously I was using `project.members.select { |member| member.user_id == current_user.id }[0]` — Purplejacket, Sep 19 '11 at 16:04
A couple colleagues suggested I write code to select the member records instead of the project records, something like `Member.includes(:projects).where(:join_code => nil, :activated => true, :users => {:user_id => user.id})` but I'm getting an `ActiveRecord::ConfigurationError` with that. — Purplejacket, Sep 19 '11 at 16:08

score 1 · Answer 2 · answered Sep 19 '11 at 17:41

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

score 1 · Answer 3 · answered Sep 20 '11 at 15:11

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

To add the ordering use `members.live.includes(:project).order('projects.name').group_by(&:project)`. — Tom Close, Sep 22 '11 at 15:02

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

3 Answers3