writing query inside map

i am trying to display category based exam stats display using below query,

$categoryDetails = QuestionCategory::whereIn('question_category_id',$categories)->get();

        $sortcollection = $categoryDetails->map( function($category) use($test_id,$user_test_id,$user_tests_det) {

            $catcorrectCount = UserAnswer::join('questions','user_answers.question_id','=','questions.question_id')->where('questions.test_id',$test_id)->where('questions.language',$user_tests_det->test_language)->where('user_answers.user_test_id',$user_test_id)->where('questions.question_category',$category->question_category_id)->whereRaw('user_answers.user_answer = questions.correct_option')->count();
            
            $catincorrectCount = UserAnswer::join('questions','user_answers.question_id','=','questions.question_id')->where('questions.test_id',$test_id)->where('questions.language',$user_tests_det->test_language)->where('user_answers.user_test_id',$user_test_id)->where('questions.question_category',$category->question_category_id)->whereRaw('user_answers.user_answer != questions.correct_option')->count();
            
            $cattotalAttempted = UserAnswer::join('questions','user_answers.question_id','=','questions.question_id')->where('questions.test_id',$test_id)->where('questions.language',$user_tests_det->test_language)->where('questions.question_category',$category->question_category_id)->where('user_answers.user_test_id',$user_test_id)->count();

            $cattotalQuestion = Question::where('test_id',$test_id)->where('language',$user_tests_det->test_language)->where('question_category',$category->question_category_id)->count();


            $catmarksDetails = json_decode($this->getMarksScoredDetailsTrait($user_tests_det,$cattotalQuestion,$catcorrectCount,$catincorrectCount),true);

            $cat_not_answer = $cattotalQuestion - $cattotalAttempted;

            return [
                'category_id' => $category->question_category_id,
                'category_name' => $category->question_category_name,
                'catcorrectCount' => $catcorrectCount,
                'catincorrectCount' => $catincorrectCount,
                'cattotalAttempted' => $cattotalAttempted,
                'cattotalQuestion' => $cattotalQuestion,
                'catnotattempted' => $cat_not_answer,
                'cattotalmarks' => $catmarksDetails['totalMarks'],
                'catmarksscored' => $catmarksDetails['marksScored'],
            ];

        });

Here the concept is every question has a single category and i am displaying category based stats like category 1 => correct , incorrrect, not attempted, total marks etc.

i have used queries inside the map and i am getting proper result but i want to know is this correct way to do this? any improvisations?

aurawindsurfing

5 years ago

Level 50

@deekshith I saw your question this morning. When you look at it it is a monnster query that only waits to break ;-)

Try moving your counts to relations first:

$catcorrectCount = UserAnswer::join('questions','user_answers.question_id','=','questions.question_id')->where('questions.test_id',$test_id)->where('questions.language',$user_tests_det->test_language)->where('user_answers.user_test_id',$user_test_id)->where('questions.question_category',$category->question_category_id)->whereRaw('user_answers.user_answer = questions.correct_option')->count();

Should become just:

UserAnswer::catcorrect()->count();

That would be a good start for me.

Deekshith

5 years ago

Level 5

Thanks for the reply. and here i have to match based on column values and put where condition thats why i have used join here. can i right it in nested where condition?

Please or to participate in this conversation.