Rails ActiveRecord内连接子查询

Question

我在使用Rails的ActiveRecord查询接口复制具有内部联接子查询的查询时遇到了困难。 我该如何复制以下内容：

SELECT ass.name, COUNT(DISTINCT a.question_id) AS 
  answered_questions, tq.total_questions
  FROM assessments AS ass
  INNER JOIN  (SELECT ass.id, COUNT(q.id) AS total_questions FROM                         
    questions AS q INNER JOIN assessments AS ass ON ass.id=q.assessment_id 
    GROUP BY 
    ass.id) as tq ON tq.id=ass.id
  INNER JOIN questions AS q ON q.assessment_id=ass.id
  INNER JOIN answers AS a ON a.assessment_id=ass.id AND a.question_id=q.id
  INNER JOIN org_assesments AS oa ON ass.id=oa.assessment_id
  INNER JOIN users AS u ON oa.org_id=u.org_id AND       
    a.user_id=u.id
  WHERE  u.id=1
  GROUP BY ass.name, tq.total_questions
  ORDER BY ass.created_at DESC
  LIMIT 10

我似乎无法使用查询构建器中的子查询使其正常工作。没有子查询，我有以下代码，它可以工作并给我评估标题和答题数：

Question.joins(:assessment => {:org_assessments => {:org => :users}}).joins(:answers)
                 .where(answers:{:user_id => params[:id]})
                 .distinct('answers.question_id').group(['assessments.name']).count()

我该如何编写代码以包含上面原始 SQL 中的子查询呢？

- Rob Irvin

---

这句话应该是以 Assessment.joins... 开头吧？ - Md. Farhan Memon

这段代码 Assessment.find_by_sql("Above query") 有效吗？ - Md. Farhan Memon

我认为你应该查询评估而不是问题，像这样：->Assessment.joins(:questions,:answer, org_assesments: :user).where(users:{id: 1}).distinct('answers.question_id').group(['assessments.name']).order('assessments.created_at DESC').count() - Divya Sharma

Answer 1

您可以将子查询作为字符串传递给joins方法。

sub_query =
  TotalQuestion.
    joins(:assessments).
    group('assessments.id').
    select('assessments.id, COUNT(q.id) as total_questions').to_sql

Question.joins("(#{sub_query}) as tq on tq.id=ass.id")

而且你可以将其与查询的其他部分组合：

Question.
    joins(:assessment => {:org_assessments => {:org => :users}}).joins(:answers).
    joins("(#{sub_query}) as tq on tq.id=ass.id").
    where(answers:{:user_id => params[:id]}).
    distinct('answers.question_id').group(['assessments.name']).count()

- Ilya Lavrov