Laravel Nested Query

Question

i'm having hard time getting the right query for this in laravel.

I have table Exams.

id | exam_name 
---|------------
1  | First Exam
2  | Second Exam
3  | Third Exam

And Students score table

id  | exam_id | score
----|---------|-------
1   | 1       | 15
2   | 1       | 12
3   | 1       | 10
4   | 2       | 7
5   | 2       | 16
6   | 2       | 13

And i want to get the average scores for all exams that looks like

exam_name   | average_score
------------|--------------
First Exam  | 12.33
Second Exam | 12
Third Exam  | 0 or NULL

I have tried left join but it returns multiple rows per exam. I can also think of using nested Query but don't know how it works in Laravel.

FULL STACK DEV · Accepted Answer · 2018-04-28 06:01:16Z

2

DB::table('exams')
   ->leftJoin('student_score','exam_id','=','exams.id')
   ->select('exam_name', DB::raw('AVG(score) as average_score'))
   ->groupBy('exam_name')->get();

You can get the AVG score by using above query.

Hope this helps.

answered Apr 28, 2018 at 6:01

FULL STACK DEV

16k6 gold badges49 silver badges67 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Yogesh Sharma · Accepted Answer · 2018-04-28 06:16:08Z

0

Do the left join with Students table :

select e.exam_name, avg(s.score) avg_score 
from Exams e 
left join Students  s on s.exam_id = e.id
group by e.exam_name;

You can also use subquery with correlation approach

select *, 
       (select avg(s.score) from Students where e.exam_id = id) avg_score 
from Exams e;

edited Apr 28, 2018 at 6:16

answered Apr 28, 2018 at 6:04

Yogesh Sharma

50.2k5 gold badges31 silver badges53 bronze badges

Comments

Wim Van Dooren · Accepted Answer · 2018-04-28 06:31:41Z

0

You don't need to join tables at all. You can solve this with plain laravel.

// in your controller
$exams = Exam::with('students')->get();

// in your view you should replace this with blade
foreach ($exams as $exam) {
    $exam->students->avg('score'); // Average score for each exam
}

Your exam model should have:

public function students()
{
    return $this->hasMany(student::class);
}

and your students class must have the inverse relation:

public function exam() {
    return $this->belongsTo(exam::class);
}

answered Apr 28, 2018 at 6:31

Wim Van Dooren

14 bronze badges

Collectives™ on Stack Overflow

Laravel Nested Query

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related