Suppose I have a Laravel 5.5 app with four models:
Class, Student, Assignment and AssignmentSubmission
A Class belongs to many Students, a Student belongs to many Classes and an AssignmentSubmission belongs to an Assignment, a User, and a Class.
So the tables look like this:
classes:
id
name
students:
id
name
class_student:
class_id
student_id
assignments:
id
name
assignment_submissions:
id
class_id
student_id
assignment_id
Now, in a query builder class I need to construct a query that returns the Students that belong to a Class with an extra column called total_assignment_submissions with a tally of how many assignments that student has submitted for that class only.
My first port of call was the following:
$class->students()->withCount('assignmentSubmissions')->get();
But that returns the total assignment submissions for that student for all classes.
The following mySql query returns the right data:
SELECT *,
(SELECT Count(*)
FROM `assignment_submission`
WHERE `student_id` = `students`.`id`
AND `class_id` = ?) AS total_assignment_submissions
FROM `students`
INNER JOIN `class_student`
ON `students`.`id` = `class_student`.`student_id`
WHERE `class_student`.`class_id` = ?
But I can't seem to get the selectSub() addSelect() and raw() calls in the right order to get what I want. It seems that I only do it with raw queries but the event id will be unsanitized or otherwise I can do it but it only returns the assignment_count field and not the rest of the fields for the student.
$class->students()
->selectSub(
AssignmentSubmission::selectRaw('count(*)')
->whereClassId($class->id)->getQuery(),
'total_assignment_submissions'
)->toSql();
There must be a way to do this. What am I missing here?