The desire to boot from "has many pass-throughs" - do I need Arel?

Question

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

More simply:

 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 

Purpose . For a specific user, I want to receive a request that will receive all the projects, and only download the entries of the participants who associate these projects with the user.

So far I have this method in user.rb that executes the request:

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

But this is not enough. I would like 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, as a rule, I will have project.members , but in my context, I'm only interested in this entry alone , which refers to the user.

Here is what I think 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 it in Arel (or ActiveRecord)?

Answer 1

I might have something like the answer here, namely that the ActiveRecord code I wrote seems pretty reasonable. Again, here is this query:

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

When launched through the user interface with sample data, it generates this result from the 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 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 %> 

This expression for getting an item 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 are displayed on the Rails server output, So I assume my problem n + 1 solved.

Answer 2

It seems you expect that it will have no more than one active member connecting each user with the project. If so, then 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 opinion:

 <% 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 want to add an additional connection for your usage statistics, you can do this:

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

Answer 3

Add an association called live_members to 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 to 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