calculate how many students answered this question with this answer in eloquent

@snapey @jlrdw can you guys please suggest any idea please

@abdulbazith , i think we need to use raw query for this requirment,

get correct answer count and wrong answer count

select question.id,SUM(IF(sesd.student_answer==question.corrent_answer,1,0)) as correct_answer_count,SUM(IF(sesd.student_answer==question.corrent_answer,0,1)) as wrong_answer_count from question left join student_exam_sub_details as sesd on(sesd.question_id=question.id) GROUP BY sesd.question_id;

i think , it will help

Something like this should work

select
    count(case when  student_answer = correct_answer then 1 end) as correct_answers,
    count(case when student_answer != correct_answer then 1 end) as incorrect_answers
from student_answers;

You will have to change the column names to match your table.

@gopalsharma @tray2 thank you guys. thank you so much let me try it. now chart is an important issue for me

if possible can you guys please respond to this

Link:

https://laracasts.com/discuss/channels/laravel/how-to-generate-charts-using-barryvdhlaravel-dompdf-and-make-it-report

@tray2 @gopalsharma thank you guys for your response.

My StudentExamMain Model

  public function studentexamsubdetails()
    {
        return $this->hasmany('App\StudentExamSub', 'exam_main_id');
    }

My StudentExamSub Model

   public function studentexammaindetails()
    {
        return $this->belongsTo('App\StudentExamMain', 'exam_main_id');
    }

My AssessmentMain model

  public function assessmentsubdetails()
    {
        return $this->hasmany('App\AssessmentSub','assessment_id');
    }


    public function studentassessments()
    {
        return $this->hasMany('App\StudentExamMain','assessment_id');
    }

My AssessmentMain table with columns

id	name	mark_per_question duration date
1	A1		5					60mins
2	A2		2					20mins

My StudentExamMain Table

id	assessment_id	student_id	result_status
1	1				1			0
2	1				2			0
3	1				3			0
.
.
.
30	1				30			0

Total 30 students in user table. and 30 student wrote the exam. their answers for each question is given in StudentExamSub table

My StudentExamSub Table contains

id	main_exam_id	question_id	student_answer
1	1				1				A
2	1				2				B
3	1				3				D
4	1				4				A
5	1				5				C
.
.
50	1				50				A

Total 50 questions. so student 1 has answered all the 50 questions. Now i need to check how many right and how many wrong. if a question is right then mark_per_question must be multipled with it.

My QuestionDetails Model

  public function studentExamsub()
    {
        return $this->hasMany('App\StudenExamSub', 'question_id');
    }

My StudentExamSub model

 public function questiondetails()
    {
        return $this->belongsTo('App\QuestionDetails','question_id');
    }

so what i did is

   $studentexams = StudentExamMain::with('studentexamsubdetails')->where('assessment_id', 2)->get();

in my Blade file

  @foreach ($studentexams as $studentexam)  //loop the array. get the first student and move inside

                                @php $totalmark=0;@endphp	//declared a varaible to save totalmark of a single student

                                @php $totalques=0;@endphp	//declared a varaible to calculate total questions

                                @foreach($studentexam->studentexamsubdetails as $question) // loop to run answers of first student

                                @php $range=0; @endphp 

                                @php $totalques=$totalques+1; @endphp 

                                @if($question->student_answer==$question->questiondetails->correct_answer) //checking the answers of each question written by student with correct answer in QuestionDetails table

                                @php $totalmark=$totalmark+1; @endphp // if it is correct increment to 1. but here i need to multiply the mark_per_question with correct answer. and also mark should display is descending or ascending order.

                                @endif

                                @endforeach

                                @php $range=($totalmark/$totalques)*100 @endphp


{{ $studentexam->user->name  }}// display student name

 {{ number_format($range,2) }} // display mark calculated to 100%

@endforeach

this query works fine. but i know this is too long process. iam collapsing myself. can i do all the process in controller and and just pass the values alone.

my expected output

Tray- 98%
gopalsjarma- 86%
Abdul- 85%
Andrews- 70%

.
.
.
.
.
like this in descending order i need the output


Kindly help pleasee