Use Laravel Eloquent joining

Hello I want to retrieve only rows of quizzes that the student didn’t take yet When he completes a quiz the variable done in QuizStudent table is going to become 1

Quiz table

My relationships: Quiz Modal:

 public function quizstudents(){
        return $this->hasMany('AppQuizstudent');

    }

QuizStudent Modal:

  public function quiz(){
        return $this->belongsTo('AppQuiz','quiz_id');

    }

I tried to do it using Query Builder It’s working

 $idUser = Auth::User()->id;
        $quizs= DB::table('quizzes')
       ->join('quizstudents', 'quizzes.id', '=', 'quizstudents.quizze_id')
       ->select('quizzes.id')
       ->where([['quizstudents.student_id','=',$idUser],['quizstudents.done','=','0']])

       ->distinct()
        ->get();

but the problem is that I get the error on my view

Undefined property: stdClass::$classe

Because in view I use Eloquent style example: $quiz->answer and because I want to use paginate etc ..

So i tried to convert the QueryBuilder code above to eloquent I tried this:

   $quizs= Quiz::with('quizstudents')->whereIn('classe_id', $classes)->where([['quizstudents.student_id','=',$idUser],['quizstudents.done','=','0']])->orderBy('created_at','desc')->paginate(10);

But I get the error

Column not found: 1054 Unknown column 'quizstudents.student_id' in 'where clause' (SQL: select count(*) as aggregate from `quizzes` where `classe_id` in (1) and (`quizstudents`.`student_id` = 79 and `quizstudents`.`done` = 0))

Thank you for your time and sorry in advance

Edit: The two answers in bottom are amazing they are both working thanks for the people who helped, please don’t miss the part to set up your foreign key in the relationship if it’s different than what’s normal A little explanation I created the modal Quiz and the migration and controller in the same time using modal:make Quiz -mc And didn’t realise that the plural of quiz is quizzes so when I’ve set the foreign key I thoughet about quizze_id will be the default but Laravel is too smart and know that it should be quiz_id as default, the luck on english can hurt you beware of this And sorry for my bad english

Answer

You are not joining you Eloquent model, if you join would also get more rows than expected, you solve that by using distinct. An easy sub query solution, is to use whereHas() instead.

Quiz::with('quizstudents')
    ->whereIn('classe_id', $classes)
    ->whereHas('quizstudents', function ($query) use ($idUser) {
        $query->where('student_id', $idUser);
        $query->where('quizstudents.done', '0');
    })
    ->orderBy('created_at','desc')
    ->paginate(10);

This is not faster than joins, but achieves it fairly simple and for a start in my opinion is the way to go.

Advertisement

Answer