Laravel Query Builder to COUNT by Grouping Results that are Common

Question

The app that I am building allows teachers to assess their students. They can have any number of students within any number of classes. (For instance, Teacher A may teach only science to one class of 25 students but Teacher B may teach 4 classes of 30 students 7 different subjects.) My end goal is to graph the performance data of any selected class for a teacher. This is what that graph might look like:

I have a MySQL table with a series of data. The structure for it looks like this:

id   user_id  room_id  student_id  subject_id  unit_id  outcome_id  assessed_level
200  28       50       69          23          203      849         4
201  28       50       66          23          203      849         3
202  28       50       76          23          203      849         2
203  28       50       69          23          212      849         4
204  28       50       66          23          212      849         1
205  28       50       76          23          212      849         3

etc.

Within a view, I want to grab all of the students for each of the subjects and determine how many achieved 1-4 as an average of their marks. They may have been assessed any number of times in a subject, and I need to output data for all of the subjects that are taught in this chosen classroom, so I determined that I need to run it all through a loop.

I am using the following to get the data:

for($x=0; $x<count($students_of_room); $x++) {
    $assessment_data[] = DB::table('student_assessments') 
        ->join('subjects', 'student_assessments.subject_id', '=', 'subjects.id')
        ->select('student_assessments.student_id as student_id', 'subjects.name as subject_name', DB::raw('round(avg(student_assessments.assessed_level)) AS average')) 
        ->where('student_assessments.user_id', Auth::id()) 
        ->where('student_assessments.room_id', $selected_room->id) 
        ->where('student_assessments.student_id', $students_of_room[$x]->id) 
        ->groupBy('student_assessments.subject_id')
        ->get();
}

which produces these results:

[{"student_id":69,"subject_name":"Science","average":"4"}] [{"student_id":66,"subject_name":"Science","average":"3"}] [{"student_id":76,"subject_name":"Science","average":"2"}] [{"student_id":67,"subject_name":"Science","average":"3"}] [{"student_id":64,"subject_name":"Science","average":"3"}] [{"student_id":68,"subject_name":"Science","average":"2"}] [{"student_id":75,"subject_name":"Science","average":"3"}] [{"student_id":73,"subject_name":"Science","average":"3"}] [{"student_id":74,"subject_name":"Science","average":"2"}] [{"student_id":70,"subject_name":"Science","average":"3"}] [{"student_id":65,"subject_name":"Science","average":"3"}] [{"student_id":62,"subject_name":"Science","average":"2"}] [{"student_id":63,"subject_name":"Science","average":"3"}] [{"student_id":71,"subject_name":"Science","average":"3"}] [{"student_id":72,"subject_name":"Science","average":"1"}]

I would like to be able to group these results together so that all of the students who have an average of 4 are together, the 3s are together, the 2s, and the 1s.

From the example output above, I would have 1 student at a 4, 9 students at a 3, 4 students at a 2, and 1 student at a 1.

I would like it to look something like this:

[{"subject_name":"Science","level":"4","num_students":"1"}] [{"subject_name":"Science","level":"3","num_students":"9"}] [{"subject_name":"Science","level":"2","num_students":"4"}] [{"subject_name":"Science","level":"1","num_students":"1"}]

How could I go about getting this result?

Also, if there are other issues where my code could be improved, I am open to that as well. Thanks!

I don't completely follow your logic/problem, but my guess that you don't need a loop here. Let MySQL worry about looping, and you only need to formulate the correct query. — Tim Biegeleisen
@TimBiegeleisen as he said, You have to make complex query which will execute faster than the loop you've made — Sagar Gautam
The reason that I am looping is that I need to get an indeterminate number of records within an indeterminate number of subjects. I'll update my question. — Sheldon Scott
@TimBiegeleisen and Sagar: I have updated the question a bit. I hope it's more clear now. My apologies, too - I am a hobbyist programmer trying to learn the ropes the best I can ;) — Sheldon Scott

Sagar Gautam Sagar Gautam · Accepted Answer · 2018-03-01T02:59:51

I think following query will give you the first result, please have a try.

DB::table('student_assessments')
->join('subjects', 'student_assessments.subject_id', '=', 'subjects.id')
->where('student_assessments.user_id', Auth::id()) 
->where('student_assessments.room_id', $selected_room->id)
->select('student_assessments.id','subject.name as subject_name','subject.id as subject_id',DB::raw('round(avg(student_assessments.assessed_level)) AS average'))
->groupBy('subject_id','id')
->get();

Laravel Query Builder to COUNT by Grouping Results that are Common

1 Answers