title: Creating a Rails 3 scope that joins to a subquery
body: <p>First off, I'm a Ruby/Rails newbie, so I apologize if this question is basic.</p>
  
  <p>I've got a DB that (among other things) looks like this:</p>
  
  <pre><code>organizations { id, name, current_survey_id }
  surveys { id, organization_id }
  responses { id, survey_id, question_response_integer }
  </code></pre>
  
  <p>I'm trying to create a scope method that adds the average of the current survey answers to a passed-in Organization relation. In other words, the scope that's getting passed into the method would generate SQL that looks like more-or-less like this:</p>
  
  <pre><code>select * from organizations
  </code></pre>
  
  <p>And I'd like the scope, after it gets processed by my lambda, to generate SQL that looks like this:</p>
  
  <pre><code>select o.id, o.name, cs.average_responses
  from organizations o join
  (select r.id, avg(r.question_response_integer) as average_responses 
       from responses r 
       group by r.id) cs on cs.id = o.current_survey_id
  </code></pre>
  
  <p>The best I've got is something like this:</p>
  
  <pre><code>current_survey_average: lambda do |scope, sort_direction|
    average_answers = Responses.
       select("survey_id, avg(question_response_integer) as average_responses").
       group("survey_id")
    scope.joins(average_answers).order("average_responses #{sort_direction}")
  end
  </code></pre>
  
  <p>That's mostly just a stab in the dark - among other things, it doesn't specify how the scope could be expected to join to <code>average_answers</code> - but I haven't been able to find any documentation about how to do that sort of join, and I'm running out of things to try.</p>
  
  <p>Any suggestions?</p>
  
  <p>EDIT: Thanks to Sean Hill for the answer. Just to have it on record, here's the code I ended up going with:</p>
  
  <pre><code>current_survey_average: lambda do |scope, sort_direction|
    scope_table = scope.arel.froms.first.name
    query = &lt;&lt;-QUERY
      inner join (
       select r.survey_id, avg(r.question_response_integer) as average_responses
        from responses r
        group by r.survey_id
      ) cs
      on cs.survey_id = #{scope_table}.current_survey_id
    QUERY
    scope.
      joins(query).
      order("cs.average_responses #{sort_direction}")
  end
  </code></pre>
  
  <p>That said, I can see the benefit of putting the <code>averaged_answers</code> scope directly onto the Responses class - so I may end up doing that.</p>