Laravel get count with nested where condition

I Have three tables,

user_tests => user_test_id, user_id, marks_scored,time_taken, language
user_answers => user_answer_id, user_test_id, user_answer, time_taken, question_id,
question_table => question_id, question_title,description,option_a, option_b,option_c, option_d,correct_option, language, question_level

My requirement is to display all easy , medium, hard question level questions count. (here we should consider user_tests table because this table holds user completed tests)

First i am fetching details like below,

$getusertests = UserTest::where('course_id',$course_id)->where('attempt',1)->where('user_test_status',4)->where('user_id','=',uid())->groupBy('test_id')->get(['test_id','user_test_id','test_language']);
     
      $usertestids = $getusertests->pluck('user_test_id');
      $testids = $getusertests->pluck('test_id');
      $languages = $getusertests->pluck('test_language');

Above query returns all tests user completed and using pluck i am getting test id and user_test_id and languages in array format.

Now i have created one trait function to retrieve easy level questions like below,

$easy_total_questions = $this->getQuestionLevelAnalysis('Easy',$usertestids,$testids,$languages);

In trait i have written code like below,

public function getQuestionLevelAnalysis($level,$user_testids,$testids,$languages)
  {
    $easy_total_questions = UserTest::withCount(['testdet.fetchquestions' => function($q) use($languages){
                                  $q->where('question_level','Easy')
                                  ->whereIn('language',$languages);
                              }])->whereIn('test_id',$testids)
                                ->whereIn('user_test_id',$user_testids)
                                ->where('attempt',1)
                                ->where('user_test_status',4)
                                ->where('user_id','=',uid())
                                ->groupBy('test_id')
                                ->get();

    return $easy_total_questions;
  }

UserTest.php

  public function testdet()
    {
      return $this->belongsTo('App\Test','test_id','test_id');
    }

Test.php

public function fetchquestions()
    {
      return $this->hasMany('App\Question','test_id','test_id');
    }

i am getting below error,

Call to undefined method Illuminate\Database\Query\Builder::testdet.fetchquestions()

if i remove withcount and change to with then it will return. please check and is this correct way? and any shortest method to achieve this?

Deekshith

5 years ago

Level 5

Now i am trying to get only correct answers using below query,

$cat['easy_correct_attempt'] = DB::table('user_answers')
        ->leftjoin('questions','user_answers.question_id','=','questions.question_id')->leftjoin('question_categories','questions.question_category','=','question_categories.question_category_id')->leftjoin('user_tests','user_answers.user_test_id','=','user_tests.user_test_id')
        ->whereRaw(" user_answers.user_answer = questions.correct_option"
                . " and questions.question_level = 'Easy' and user_tests.user_id=$ckeck_user_id and  user_tests.user_test_id in ('$ids') and questions.language in ('$languageids')")
        ->count();

is there any way i can replace this with relationship or Eloquent way?

Please or to participate in this conversation.