Laravel nested relationships from a ternary pivot table

I have three entity tables, student, course, and semester. They are linked together by a ternary pivot table - that is, each row represents "student X taking course Y in semester Z":

# Table course_students

| student_id | semester_id | course_id |
|------------|-------------|-----------|
|     18     |     4       |    80     |
|     18     |     8       |    64     |
|     18     |     8       |    60     |

From this, I'd like to build a nested collection such that:

• Each student has a collection containing the semesters in which that student had at least one course;
• Each semester for a given student has a collection containing the courses the student took in that semester.

So, for the above table, I'd like to call something like Student::find(18)->with('coursesBySemester') and get a collection that looks like:

{
    "id": 18,
    "first_name": "Wesley",
    "last_name": "Snipes",
    "email": "wes@expendables.com",
    "semesters": [
        {
            "id": 4,
            "name": "Fall 2014",
            "pivot": {
                "student_id": 18,
                "semester_id": 4
            },
            "courses": [
                {
                    "id": 80,
                    "title": "Game Theory",
                    "pivot": {
                        "semester_id": 4,
                        "course_id": 80,
                        "student_id": 18
                    }
                },
            ]
        },
        {
            "id": 8,
            "name": "Fall 2016",
            "pivot": {
                "student_id": 18,
                "semester_id": 8
            },
            "courses": [
                {
                    "id": 64,
                    "title": "Introduction to Calculus with Applications",
                    "pivot": {
                        "semester_id": 8,
                        "course_id": 64,
                        "student_id": 18
                    }
                },
                {
                    "id": 60,
                    "title": "Introduction to Finite Math 1",
                    "pivot": {
                        "semester_id": 8,
                        "course_id": 60,
                        "student_id": 18
                    }
                }
            ]
        }
    ]
}

What I've tried

I can get most of the way there with the following relationship defined in my Student model:

/**
 * Load a collection of semesters during which this student was enrolled in at least one course, and the courses that they took in each semester
 */
public function coursesBySemester()
{
    return $this->belongsToMany('UserFrosting\Sprinkle\Btoms\Model\Semester', 'course_students')
    ->with(['courses' => function ($query) {
        return $query->where('course_students.student_id', $this->id);
    }])
    ->groupBy('semester_id');
}

The Semester model has the following relationship defined:

/**
 * Lazily load a collection of courses that were taken in this semester.
 */
public function courses()
{
    return $this->belongsToMany('UserFrosting\Sprinkle\Btoms\Model\Course', 'course_students')->withPivot('student_id');
}

The problem is that when I call with('courses') in my coursesBySemester relationship, it retrieves all courses that any student took in that semester. I only want the courses that the parent student took in that semester.

As you can see I tried to constrain that relationship by using where('course_students.student_id', $this->id), but $this->id does not actually have any value set in the context of the relationship. I've also tried the wherePivot method, but again, I don't know how to dynamically set that constraint based on the id of the parent Student model.

I realize that I could just create a helper that manually goes through and builds the collection I want, but I'd really like to implement this as a single relationship so that I can use it fluently in other query builder expressions.

via alexw