CODEWITHSUNDEEP
phpsqllaraveleloquentsubquery
WebSpanner
WebSpanner

Reputation: 424

Adding a count column for a filtered relationship to a select with a subquery in Laravel Eloquent

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?

Upvotes: 2

Views: 1634

Answers (1)

N Mahurin
N Mahurin

Reputation: 1446

withCount() can be constrained. 

$class->students()->withCount(['assignmentSubmissions' => function($q) use($course_id){
    $q->where('course_id', $course_id);
}])->get();

This will limit the count to that particular course.

Upvotes: 2

Related Questions