Joining 3 models together in Rails to display in a view iterating over one model

I have a fairly standard 'has_many :x, :through => :y' relationship with a user, a problem, and a completed_problem which acts as the association between the two:

/models/user.rb

class User < ActiveRecord::Base
  has_many :completed_problems
  has_many :problems, :through => :completed_problems    
end

/models/problem.rb

class Problem < ActiveRecord::Base
  belongs_to :wall
  has_many :completed_problems
  has_many :users, :through => :completed_problems    
end

/models/completed_problem.rb

class CompletedProblem < ActiveRecord::Base
  belongs_to :user
  belongs_to :problem

  v开发者_开发知识库alidates_presence_of :user
  validates_presence_of :problem
end

My complication is that data in each of these models impacts the display. I'm looking to display a list of problems on each wall, and for each problem in that list to use or show:

• problem.id
• problem.name
• time since the current user completed the problem last
  • if no logged in user, some text
  • if user hasn't completed that problem, some other text

A (very ugly) first pass at the view is as follows:

/views/walls/show.html.erb

<% @wall.problems.each do |problem| %>
    <a id=<%= "problem_#{problem.id}" %>>
      <h3><%= problem.name %></h3>
      <p><%= "#{time_ago_in_words(problem.last_complete_by_user(current_user))} ago" if current_user && problem.last_complete_by_user(current_user) %></p>
    </a>
  </li>
<% end %>

I've since overwritten it, but problem.last_complete_by_user (seen in the above snippet) was an attempt to use the problem object to find all the related completed_problems, with the user as an argument, in order to identify the 'updated_at' value for the most recently updated completed_problem for that particular problem and user.

Of course this isn't ideal because it'll be a separate query for each item in the list - I assume the preferred solution would be a method in the wall controller or model that joins across all 3 tables and returns a new array for the view to iterate over. Unfortunately I've spent too long bouncing between :join, :include and :find_by_sql without a solution.

Can someone at least lead me in the right direction for how to get this view working properly?

This is how I would solve the problem. It may not be the most efficient solution, but it's clean and easy to refactor when the time comes. I haven't tried the code, but it's probably not too far off. If you go this route and run into performance problems, I would look into fragment caching before adding a bunch of crazy SQL.

Models:

class User < ActiveRecord::Base
  has_many :completed_problems
  has_many :problems, :through => :completed_problems

  # Finds the last completed problem
  def last_completed_problem(problem)
    problems.order('created_at DESC').where(:problems => {:id => problem}).limit(1).first
  end
end

# No Changes
class Problem < ActiveRecord::Base
  belongs_to :wall
  has_many :completed_problems
  has_many :users, :through => :completed_problems
end

# No changes
class CompletedProblem < ActiveRecord::Base
  belongs_to :user
  belongs_to :problem

  validates_presence_of :user
  validates_presence_of :problem
end

app/controllers/walls_controller.rb:

class WallsController < Application::Controller
  def show
    @wall = Wall.find(params[:id]).includes(:problems)
  end
end

app/helpers/wall_helper.rb:

module WallHelper
  def show_last_completed_problem_for_user(user, problem)
    return "You are not logged in" if current_user.nil?

    completed = user.last_completed_problem(problem)

    return "You have not completed this problem" if completed.nil?

    time_ago_in_words(completed.created_at)
  end
end

app/views/walls/show.html.erb:

<%= render :partial => 'problem', :collection => @wall.problems %>

app/views/walls/_problem.html.erb:

<li>
  <a id=<%= "problem_#{problem.id}" %>>
    <h3><%= problem.name %></h3>
    <p><%= show_last_completed_problem_for_user(current_user, problem) %></p>
  </a>
</li>

So you want the LATEST completed problem, of which there may be many... or none.

We can do that by left-joining to completed_problems (in case there's not one there) then grouping on user/problem. The grouping means you get only one record per user/project. One final trick -- you need to specify your columns in 'select' so that we get the normal project fields and another for the last_attempted.

@problems = Problem.join("LEFT OUTER JOIN problems ON completed_problems.problem_id = problems.id").
                    group(:user_id, :problem_id).
                    select("projects.*", "MAX(completed_problems.updated_at) as last_attempted").
                    where(:wall_id => params[:wall_id])