我在使用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 中的子查询呢?
Assessment.joins...
开头吧? - Md. Farhan MemonAssessment.find_by_sql("Above query")
有效吗? - Md. Farhan Memon