Laravel Fetch MySQL Results by Relevant Table Column Value

Question

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.

Welcome to Stack Overflow! Please take the tour to learn how Stack Overflow works. Read How to Ask and Minimal, Reproducible Example for know how to improve the quality of your question. — André Walker
– André Walker, Commented Jan 20, 2021 at 18:13
seems like you need to learn a couple about query and JOINS. Laravel Joins | QUERIES JOINS — André Walker
– André Walker, Commented Jan 20, 2021 at 18:17
@AndréWalker I tried that but I couldn't achieve. 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() — I. Kaya
– I. Kaya, Commented Jan 20, 2021 at 18:25

M Khalid Junaid · Accepted Answer · 2021-01-20 18:54:12Z

1

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();

answered Jan 20, 2021 at 18:54

M Khalid Junaid

64.6k10 gold badges93 silver badges120 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

I. Kaya Over a year ago

That works perfect! Thank you so much! But I have another question about first code. You used 'answers.question (every column in questions table I guess).quiz (same as before)'. But what if I have different table name than model? (Like in different language). How can I use that line in case of that situation?

M Khalid Junaid Over a year ago

@I.Kaya In first query the navigation properties of models are used these are not table names, like in user model there will be a relationship method which points to related answers for that user, in answer model there will be a relationship method which points to question model and same in question model there will be a relationship method that points to quiz model, check docs Querying Relationship Existence

Collectives™ on Stack Overflow

Laravel Fetch MySQL Results by Relevant Table Column Value

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related