How to get Random question but non repeatable

Hi, i am making a simple quiz. i want to show one question at a time on screen. in my questions table , i have all quiz questions and in answers table 4 options for each question and in results table record of each questions attempt by user. so i should get one random question at a time which is not already in results table

questions table: id,question,created_at,updated_at

answers table:id, question_id,answer,is_correct,created_at,updated_at

results table: id, question_id, users_id, answer ,created_at,updated_at

i'm saving the answer submitted by user in answer column of results table.

Now i want to show only those questions which were not attempt by that particular user. How do i query this??

This is my attempt but unable to combine in one query.

 $question = Question::with('answers')
                    ->inRandomOrder()
                    ->first(); 
                 

        $qid = $question->id;
        $result = Result::where('question_id','!=',$qid)
                       ->where('user_id','!=',1)->first();

willvincent

6 years ago

Best Answer

Level 54

Well first off, you're not running a single query anyway since you're eager loading answers, so no need to worry about that. That out of the way, invert the logic, and fetch the answered question ids first, then use for a where not in clause..

Something like this:

$asked_ids = Result::where('user_id', Auth::user()->id)
                      ->pluck('question_id');

$question = Question::with('answers')
                      ->whereNotIn('id', $asked_ids)
                      ->inRandomOrder()
                      ->first();

Can't remember if you need to tack a ->toArray() on that first query or not before using it in a where[not]In clause.

But... that should grab you the first random question not yet asked to the authenticated user

david001

6 years ago

Level 5

@willvincent Actually , i was planning to store in session do as below


$question = Question::with('answers')
                   ->inRandomOrder()
                   ->whereNotIn('id',session('questions'))
                   ->first();

$request->session()->push('questions',$request->id);
     return $request->session()->get('questions');

i will use your logic provided above

Please or to participate in this conversation.