Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

surendrasahi's avatar

Better Approach to Merge/Join Data from Two Different Pivot Tables

Hello, I am designing a quiz application with the following database pattern.

  1. questions table: id, question, correct_marks, etc

  2. quizzes table: id, name, etc

  3. quiz_questions pivot table: quiz_id, question_id

  4. quiz_sessions table: id, session_uuid, user_id, status, etc

  5. quiz_session_questions pivot table: quiz_session_id, question_id, user_response, is_correct, etc

When a user initiates a quiz, it will create a new session, and if the user answers a question, that response will be attached to the quiz_session_questions pivot table. The user can pause the quiz session and resume it later.

Now, while resuming the quiz session, I have to merge all quiz questions (from quiz_questions pivot table) with user answered questions (from quiz_session_questions pivot table).

Quiz Model:

public function questions()
{
  return $this->belongsToMany(Question::class, 'quiz_questions', 'quiz_id', 'question_id');
}

public function sessions()
{
  return $this->hasMany(QuizSession::class);
}

Question Model:

public function quizSessions()
{
  return $this->belongsToMany(QuizSession::class, 'quiz_session_questions')
            ->withPivot('status', 'is_correct', 'time_taken', 'user_answer');
}

Quiz Session Model:

public function quiz()
{
  $this->belongsTo(Quiz::class, 'quiz_id');
}

public function questions()
{
  return $this->belongsToMany(Question::class, 'quiz_session_questions')
            ->withPivot('status', 'is_correct', 'time_taken', 'user_answer');
}

Currently, I am achieving this in the following way.

public function getQuizQuestions($quiz, $session)
{
	$quiz = Quiz::findOrFail($quiz);
        $session = QuizSession::with('questions')->where('session_id', $session)->firstOrFail();

        $questions = $quiz->questions()->with(['quizSessions' => function($query) use ($session) {
            $query->where('quiz_session_id', $session->id);
        }])->get();

        return response()->json([
            'questions' => $questions,
        ], 200);
}

I know this is not the best way. Can we achieve the same with some subqueries? I am not that good at databases. I would be grateful if anyone suggests a better approach. Thank you.

0 likes
0 replies

Please or to participate in this conversation.