2

I have:

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

The scores table has a total_score field and a name field.

How can i write a scope to ask for only those evaluations which have a Score with name 'vision' and total_score of 2 and they have another Score with name 'hearing' and total_score of 3. And how can all this be generalized to ask for those evaluations which have n scores with my parameters?

In raw sql it would be like:

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)-

Here the point is to repeat this:

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

and this (by replacing s1 to s2 and s3 and so forth):

WHERE (s1.total_score >= 1)

But it should be a rails way of doing this... :) Hopefully

Wayne Conrad
  • 103,207
  • 26
  • 155
  • 191
Boti
  • 3,275
  • 1
  • 29
  • 54

1 Answers1

0

Try this:

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

Then call like this:

Evaluation.by_scores 'vision' => 2, 'hearing' => 3
Mark Huk
  • 2,379
  • 21
  • 28
  • Could you fix the typo (Hahs -> Hash), please? I can't do this because edits must be at least 6 characters. – Flexoid Apr 30 '12 at 11:39
  • How can I change the query to check if the total score is greater than my score? – Boti Apr 30 '12 at 11:42
  • That code just doubles the WHERE statements... It would generate this sql, which is not what I need: SELECT "evaluations".* FROM "evaluations" INNER JOIN "scores" ON "scores"."evaluation_id" = "evaluations"."id" INNER JOIN "target_disabilities" ON "target_disabilities"."id" = "scores"."target_disability_id" WHERE "target_disabilities"."name" = 'vision' AND "target_disabilities"."name" = 'hearing' AND (scores.total_score >= 1) AND (scores.total_score >= 2) – Boti Apr 30 '12 at 13:22
  • 1
    @Boti, another approach is not use N joins, but use group by. For example, replace `AND` into your pairs and then group by `evaluations.id` and filter by pairs count. Please, response if you wish an example with `meta_where` or `squeel`. – Mark Huk Apr 30 '12 at 17:24
  • Mark: It was a great idea. It is much more simpler to put together the query by grouping. Let me show you: sql = %q-select evaluation_id FROM (SELECT evaluation_id, COUNT(*) AS count FROM "scores" INNER JOIN "target_disabilities" AS t1 ON "t1"."id" = "scores"."target_disability_id" WHERE ('t1'.'name' = 'vision' AND (scores.total_score >= 1)) OR ('t1'.'name' ='hearing' AND (scores.total_score >= 2)) GROUP BY evaluation_id) WHERE count = 2- – Boti Apr 30 '12 at 21:37
  • 1
    Another observation: the lambda do |params|... will trigger a parser error. It should be written like scope :by_scores, (lambda do |params|...). See: http://stackoverflow.com/questions/1476678/rails-named-scope-lambda-and-blocks – Boti May 04 '12 at 10:40
  • After doing some research... It seems to me that ActiveRecord::Relation doesn't support the 'OR' in 'WHERE'-s. Or does it? It can be done easily with squeel. – Boti May 11 '12 at 20:36
  • Rails 3 has build in Areal: [see SO question](http://stackoverflow.com/questions/3639656/activerecord-or-query) – Mark Huk May 11 '12 at 21:12