[FIXED] Laravel custom query for answer and question database

Issue

I have three table called exams, questions and answers. They are connected via foreign id. I have function which i join questions and answers table. I have 1 question and multiple answers. When i join i see many same questions with 1 answer (which is normal). Is there any way to make join 1 question and multiple answers in one query(array) via query builder

Exams table

        $table->id();

        $table->string('language_code');
        $table->string('title');
        $table->integer('subcategory_id')->nullable();
        $table->string('section');
        $table->string('class');
        $table->string('subject')->nullable();
        $table->string('time')->default('60');

        $table->timestamps();

Questions table

        $table->id();

        $table->integer('exam_id');
        $table->string('q_pic')->nullable();
        $table->string('q_name')->nullable();
        $table->string('q_text');

        $table->timestamps();

Answers table

        $table->id();

        $table->integer('user_id');
        $table->integer('question_id');
        $table->text('user_answer')->nullable();
        $table->integer('user_answer_id');
        $table->integer('c_answer_id'); 

        $table->timestamps();

And here is my view function

public function view($id)
{
    $questions = DB::table('questions')
        ->leftJoin('answers','answers.question_id','=','questions.id')
        ->where('questions.exam_id','=',$id)
        ->get();

    return view('main/view',compact('questions'));
}

Solution

If you set up the relationships right, you would not need a join to achieve this.

Your relationships should look like this:

Exam model:

public function questions()
{
  return $this->hasMany(Question::class);
}

Question model:

public function exam()
{
  return $this->belongsTo(Exam::class);
}

public function answers()
{
  return $this->hasMany(Answer::class);
}

Answer model:

public function question()
{
  return $this->belongsTo(Question::class);
}

Now you can query like this:

//use App\Question;
$questionsWithAnswers = Question::where('id', $id)->with('answers')->get();

This should give you a collection of questions with related answers.

Answered By - user3532758