如何编写最有效的范围来仅询问那些具有某些特定子对象的父对象

Question

我有：

class Evaluation < ActiveRecord::Base
  has_many :scores
end
class Score < ActiveRecord::Base
  scope :for_disability_and_score,
        lambda { |disability, score|
          where('total_score >= ? AND name = ?', score, disability)
        }
end

scores表具有total_score字段和name字段。

我怎么能写一个scope，只要求那些Score名称为"视觉"且total_score为 2 且他们有另一个名称为"听力"和total_score为 3 的Score的evaluations。如何概括所有这些来要求那些与我的参数有 n 分的评估？

在原始sql中，它就像：

I managed to do it in raw sql:
sql = %q-SELECT "evaluations".*
FROM "evaluations"
INNER JOIN "scores" AS s1 ON "s1"."evaluation_id" = "evaluations"."id"
INNER JOIN "target_disabilities" AS t1 ON "t1"."id" = "s1"."target_disability_id"
INNER JOIN "scores" AS s2 ON "s2"."evaluation_id" = "evaluations"."id"
INNER JOIN "target_disabilities" AS t2 ON "t2"."id" = "s2"."target_disability_id"
WHERE "t1"."name" = 'vision' AND (s1.total_score >= 1)
      AND "t2"."name" = 'hearing' AND (s2.total_score >= 2)-

这里的重点是重复这一点：

INNER JOIN "scores" AS s1 ON "s1"."evaluation_id" = "evaluations"."id"

以及这个（通过将 S1 替换为 S2 和 S3 等）：

WHERE (s1.total_score >= 1)

但这应该是一种轨道方式... :)希望

Answer 1

试试这个：

scope :by_scores, lambda do |params|
    if params.is_a? Hash
      query = joins(:scores)
      params.each_pair do |name, score|
        query = query.where( 'scores.total_score >= ? AND scores.name = ?', score, name)
      end
      query
    end
 end

然后像这样打电话：

Evaluation.by_scores 'vision' => 2, 'hearing' => 3