How can I get the average of a relationship and then orderBy it in Laravel 7.x?
I have a table named students and a table named scores.
The primary key of the students table is id.
The table scores has a foreign key student_id to students.id The table scores also has many fields, two of them are score and year, indicating the value of score and which year did the student got this score.
In a year, a student got many scores. I want to get their average score and rank the students’ average score in a specific year.
The model "Student" has a relationship:
class Student extends Model { public function scores() { return $this->hasMany('App\Score'); } } I’ve tried: In Student Model,
class Student extends Model { public function average_score() { return $this->scores() ->selectRaw('avg(score) as score, student_id') ->groupBy('student_id'); } } In StudentCtrl,
// Select only the student’s scores in a specific year $yearQuery = function($query) use ($request) { return $query->where('year', '=', $request->year); }; $list = Student::select('id', 'name') ->with(['average_score' => $yearQuery]) ->orderBy('average_score') // <-- If I delete this it works ->paginate(15); It failed with: SQLSTATE[42S22]: Column not found: 1054 Unknown column ‘average_score’ in ‘order clause’
Here, we are going to leverage map(), groupBy(), avg(), sortBy()
$response = []; // we'll store our final response here $students = Student::select('id', 'name')->with('scores')->limit(15)->get(); $students->map(function ($student) { // groupBy student's each score by year return $student->scores->groupBy('year')->map(function ($score, $year) use ($student) { // create new array for simplicity $score = [ 'student_id' => $student->id, 'student_name' => $student->name, 'average_score' => $score->avg('score'), 'year' => $year, ]; return $score; })->toArray(); })->map(function($lists) use(&$response) { // groupBy students with each year collect($lists)->each(function($list) use(&$response){ $response[$list['year']][] = collect($list)->forget('year')->toArray(); }); })->toArray(); dd(collect($response)->map(function($list){ return collect($list)->sortBy('average_score')->all(); // you sort here })->all()); sample response
array:3 [ 2016 => array:2 [ 1 => array:3 [ "student_id" => 2 "student_name" => "Adam" "average_score" => 30 ] 0 => array:3 [ "student_id" => 1 "student_name" => "John" "average_score" => 37.5 ] ] 2017 => array:3 [ 1 => array:3 [ "student_id" => 2 "student_name" => "Adam" "average_score" => 50 ] 0 => array:3 [ "student_id" => 1 "student_name" => "John" "average_score" => 70 ] 2 => array:3 [ "student_id" => 3 "student_name" => "Tony" "average_score" => 80 ] ] 2018 => array:1 [ 0 => array:3 [ "student_id" => 3 "student_name" => "Tony" "average_score" => 100 ] ] ] Keep me posted in the comments below. Cheers!
