CODEWITHSUNDEEP
phpmysqllaravel
I. Kaya
I. Kaya

Reputation: 89

Laravel Fetch MySQL Results by Relevant Table Column Value

I have 3 tables. Quizzes, Questions and Answers. Columns I want to use are;

Quizzes > id

Questions > quizid (blongs quiz)

Answers > userid, questionid (belongs question)

So my Answers table has no QuizID column. What I want is; group answers table by user ID depending on Question ID that belongs to specified Quiz ID.

In short; I want users that solved the Quiz. But I'm in a dead end :/

My query is like that;

Answer::join('questions', 'questions.id', '=', 'answers.id')
->join('quizzes', 'questions.quiz_id', 'quizzes.id')
->join('users', 'users.id', 'answers.userid')
->where('questions.quiz_id', $id)
->select('users.*')
->groupBy('questions.quiz_id')
->get()

My mistake is at the groupBy line. I don't want to group questions. I want to group answers that belongs to Quiz's questions. But I don't know how to do that.

Upvotes: 1

Views: 30

Answers (1)

M Khalid Junaid
M Khalid Junaid

Reputation: 64466

You can get list of users that attempted specific quiz as

$users= User::whereHas('answers.question.quiz', function (Builder $query) use ($id) {
                 $query->where('id', $id);
             })->get();

Or from joined query as

$users = DB::table('users as u')
            ->join('answers as a', 'u.id', '=', 'a.userid')
            ->join('questions q', 'q.id', '=', 'a.questionid')
            ->join('quizzes qz', 'q.quiz_id', 'qz.id')
            ->where('qz.id', $id)
            ->select(['u.id','u.name','q.id as qz_id','qz.name as qz_name'])
            ->groupBy(['u.id','u.name','q.id','qz.name'])
            ->get();

Upvotes: 1

Related Questions