public function index() { $statistics = Question::selectRaw(' SUM(IF(difficulty="سهل",1,0)) AS easy, SUM(IF(difficulty="صعب",1,0)) AS hard, SUM(IF(difficulty="صعب جدا",1,0)) AS veryhard, SUM(IF(difficulty="متوسط",1,0)) AS medium, SUM(IF(level="الفهم",1,0)) AS understanding, SUM(IF(level="التذكر",1,0)) AS memory, SUM(IF(level="الاستنتاج",1,0)) AS conclusion, SUM(IF(level="تطبيق",1,0)) AS practice, SUM(IF(level="تحليل",1,0)) AS analyzing, SUM(IF(type="essay",1,0)) AS writing, SUM(IF(type="true_false",1,0)) AS choice, SUM(IF(type="single_choice",1,0)) AS single, SUM(IF(type="multiple_choice",1,0)) AS multiple ')->first(); $subjects = Subject::with(['questions' => function ($query) { $query->groupBy('questionbank_questions.type', 'subject_lessons.subject_subject_id')->selectRaw("COUNT(questionbank_questions.id) AS total,questionbank_questions.type"); }])->get(); foreach ($subjects as &$subject) { $subject->totalArticles = $subject->questions->sum(function ($question) { return $question->type == 'essay' ? $question->total : 0; }); $subject->totalOptions = $subject->questions->sum(function ($question) { return $question->type == 'true_false' ? $question->total : 0; }); $subject->totalMultiple = $subject->questions->sum(function ($question) { return $question->type == 'multiple_choice' ? $question->total : 0; }); $subject->totalOneChoice = $subject->questions->sum(function ($question) { return $question->type == 'single_choice' ? $question->total : 0; }); $subject->totalAllQuestions = $subject->questions->sum(function ($question) { return $question->total; }); } return view('questionbank::report.index', compact('subjects', 'statistics')); }