CODEWITHSUNDEEP
ruby-on-railsactiverecord
Pelle
Pelle

Reputation: 6578

Rails: finding a record by a combination of related records

In a Ruby on Rails 4 app, consider this data model:

Question:
  id: int
  # [...]
  has_many: Answers

Answer:
  id: int
  question_id: int
  # [...]
  belongs_to: Question
  has_many: Results through AnswerResult

AnswerResult:
  id: int
  answer_id: int
  result_id: int

Result:
  id: int
  # [...]
  has_many: Answers through AnswerResult

Using this model, consider this behaviour:

  1. There are multiple questions, all having multiple answers.
  2. Each specific combination of answers leads to one and only one result.

Now, here is my question:

After the user has answered all my questions, I end up having a hash of question IDs with the user's answer IDs. I want to find the result that belongs to that combination of answers.

How do I do that?

EDIT: Example: (as requested in comments)

Questions and answers, and their IDs

Gender:     [Question #1]
  - Male    [Answer   #1]
  - Female  [Answer   #2]
Age:        [Question #2]
  - < 30    [Answer   #3]
  - 30 - 65 [Answer   #4]
  - > 65    [Answer   #5]

Results:

Junior man   [#1]  bound to answers [#1, #3]
Senior man   [#2]  bound to answers [#1, #4]
Old man      [#3]  bound to answers [#1, #5]
Junior woman [#4]  bound to answers [#2, #3]
Senior woman [#5]  bound to answers [#2, #4]
Old woman    [#6]  bound to answers [#2, #5]

Input:

{ 
  1 => 2, # Gender: female
  2 => 4  # Age:    30-65
}

Function finds the result bound to answers #2 and #4

Output: Result #5: Senior woman

Upvotes: 1

Views: 264

Answers (2)

Rafa Paez
Rafa Paez

Reputation: 4860

This is a difficult problem to resolve in SQL unless you know about the concept Relational Division in SQL.

The following query provides you the expected result

SELECT results.*
  FROM results
  INNER JOIN answer_results 
    ON results.id = answer_results.result_id
  WHERE answer_results.answer_id IN (2, 4)
  GROUP BY results.id
  HAVING COUNT(results.id) = 2

SQL Fiddle

Here you are a non-tested (you might change something) possible solution in ActiveRecord

answer_ids = input.map(&:last)

Result.joins(:answer_results).where("answer_results.answer_id IN (?)",
 answer_ids).group('results.id').having('COUNT(results.id) = ?', answer_ids.size)

Upvotes: 1

veritas1
veritas1

Reputation: 9170

input = { 
  1 => 2, # Gender: female
  2 => 4  # Age:    30-65
}

answers = input.inject([]){|ary, input| ary << input[1] } # [2,4]

results = {
  "Junior man"   => [1,3],
  "Senior man"   => [1,4],
  "Old man"      => [1,5],
  "Junior woman" => [2,3],
  "Senior woman" => [2,4],
  "Old woman"    => [2,5]
}

output = results.select {|k,v| v == answers} # "Senior woman" => [2,4]

output.keys[0] # "Senior Woman"

Upvotes: 1

Related Questions