Better Approach to Merge/Join Data from Two Different Pivot Tables
Hello, I am designing a quiz application with the following database pattern.
-
questions table: id, question, correct_marks, etc
-
quizzes table: id, name, etc
-
quiz_questions pivot table: quiz_id, question_id
-
quiz_sessions table: id, session_uuid, user_id, status, etc
-
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.
Please or to participate in this conversation.