How to get a specific grade data using Laravel Eloquent?

Question

What I want:

1. To get the grade of what the user answered in the database if exists and get the grade of that answer.

What I have:

1. I have 3 tables, short_answer, sa_sa_answer, short_answer_answer.
2. Now, short_answer table has question, and each question has many answer located in the short_answer_answer table and the grade is also included there, the question can have 1 or more answer.

What code I have:

Controller

foreach($sa_question_id as $key => $value){
   $sa = ShortAnswer::with('answers')->find($sa_question_id[$key]);
   $possible_answers = [];

   foreach($sa->answers as $possible_answer){
      $possible_answers[] .= strtolower($possible_answer->answer);
   }

   if(in_array(strtolower($sa_answer[$key]), $possible_answers)){
      $grade = ShortAnswerAnswer::where('answer', $sa_answer[$key])->get();
      echo "plus +1. Grade is: " . $grade->grade . "<br>";
   }
}

The problem is:

Im just getting the answer where the answer is equal to the user's answer. But what if I have TWO same answer and different grade and obviously different question. It can select the wrong one.

Note: I'm using Laravel5.1

Update: Table Structure

short_answer
- name
- question

sa_sa_answer
- short_answer_id
- short_answer_answer_id

short_answer_answer
- answer
- grade

Update

I've already solved this issue, however no one got the bounty, but If you could answer this question, I can give you the bounty plus 2 check mark and up vote, I just really need more help with this. It is also connected with this question. The bounty will be gone in 3 days from now.

Answer 1

We can solve this problem using a single query:

$questions = ShortAnswer::with('answers')
    ->whereIn('id', $sa_question_id)
    ->whereHas('answers', function ($query) use ($sa_answer) {
        $placeholders = join(",", array_pad([], count($sa_answer), "?"));
        $query->whereRaw("LOWER(answer) IN $placeholders", $sa_answer);
    })
    ->get(); 

foreach ($questions as $question) {
    foreach ($question->answers as $answer) {
        echo 'plus +1. Grade is: ' + $answer->grade}."; 
    }
}

This optimizes out the loop so we only need to query the database once for all the questions and answers. It also solves the problem in the question because the query maintains the relationships between question and answer, so we won't inadvertently select the wrong answers for the questions.

Answer 2

This part of your code:

$grade = ShortAnswerAnswer::where('answer', $sa_answer[$key])->get();

You are getting all answers from ShortAnswerAnswer that equals to $sa_answer[$key], there is no condition to point out if the answer you get from the proper question that you expected.

So, to solve your problem, you need to point out the question as well. Like:

$grade = ShortAnswerAnswer::where(['answer' => $sa_answer[$key], 'question_id' => $key])->get();

---

P/s: This part:

foreach($sa_question_id as $key => $value){
   $sa = ShortAnswer::with('answers')->find($sa_question_id[$key]);
   ...
}

Then, $sa_question_id[$key] is exactly $value.