ruby-on-rails - 我如何编写最有效的范围来仅询问那些具有某些子对象的父对象

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 来仅请求那些 evaluations 具有名为“vision”和 total_score 的 Score 为 2，他们还有另一个 Score，名称为“hearing”，total_score 为 3。以及如何将所有这些概括为要求那些对我的参数有 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)

但这应该是一种 Rails 的方式...:) 希望

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