Have you seen @bestmomo answer here https://laracasts.com/discuss/channels/general-discussion/problem-setting-up-correct-relationship-school-school-class-student

Thanks for the link, @jlrdw. That solution is pointing to hasManyThrough, but I am trying to filter data stored in only one table. (Or am I wrong about that?)

I think what I need is some kind of groupBy + groupBy statement...

Basically, I want to get an avg for each of the students who performed at each assessed_level for each subject. (All of this data is found in one table.)

Here's what my table looks like (as I said, they never format right for me on this forum):

| 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   |     76     |     23     |   203   |    849     |        2       |
| 202 |    28   |    50   |     64     |     23     |   203   |    849     |        3       |
| 203 |    28   |    50   |     69     |     23     |   203   |    852     |        3       |
| 204 |    28   |    50   |     76     |     23     |   203   |    852     |        3       |
| 205 |    28   |    50   |     64     |     23     |   203   |    852     |        1       |
| 206 |    28   |    50   |     69     |     23     |   203   |    848     |        4       |
| 207 |    28   |    50   |     76     |     23     |   203   |    848     |        3       |
| 208 |    28   |    50   |     64     |     23     |   203   |    848     |        3       |

If I could output this the way I want, it might come out like:

student 69 would average out to a score of 4 (4 + 3 + 4 = 11 / 3 = 3.6 [ceil = 4]) student 76 would average out to a score of 3 (2 + 3 + 3 = 8 / 3 = 2.6 [ceil = 3]) student 64 would average out to a score of 3 (3 + 1 + 3 = 7 / 3 = 2.3 [ceil = 3])

So, I would have 1 student at a 4 and 2 students at a 3.

Hope that makes sense...

Okay, I think I have moved the ball forward a little bit.

I have used the following statement:

 $assessment_data = DB::table('student_assessments') 
      ->join('subjects', 'student_assessments.subject_id', '=', 'subjects.id')
      ->select('subjects.name as sub_name', 'student_assessments.assessed_level as ass_lvl', DB::raw('count(*) as stud_cnt')) 
      ->where('student_assessments.user_id', Auth::id()) 
      ->where('student_assessments.room_id', $selected_room->id) 
      ->groupBy('student_assessments.subject_id','student_assessments.assessed_level') 
      ->get();

When I dd this, wrapped in json_encode, this is what I am presented with:

"[{"sub_name":"Physical Education","ass_lvl":2,"stud_cnt":1},{"sub_name":"Science","ass_lvl":1,"stud_cnt":6},{"sub_name":"Science","ass_lvl":2,"stud_cnt":15},{"sub_name":"Science","ass_lvl":3,"stud_cnt":28},{"sub_name":"Science","ass_lvl":4,"stud_cnt":11}]"

How would I go about separating each of the subjects into it's own JSON array (multidimensional array)? Or, am I on the right path?

Ideally, I would rather see that xx% of students were a Level 1 in Science, yy% were a Level 2, etc. Baby steps...

I realized I was going down the wrong path a bit with how I was thinking about the above queries. So I started over and came up with this:

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();
            }

Now my output looks like this:

[{"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"}]

So, it's getting the average performance of each student in a subject and it all calculates out right according to a check against the data in my DB.

Next step: I would love to group/count the number of students from this query who are at a '4', the number who are at a '3', the number who are at a '2', and the number who are at a '1'.

Given the above output, that should tell me there is 1 student at a 4, 9 students at a 3, 4 students at a 2, and 1 student at a 1.

Any ideas how I might achieve this?