title: Laravel nested relationships from a ternary pivot table
body: <p>I have three entity tables, <code>student</code>, <code>course</code>, and <code>semester</code>.  They are linked together by a ternary pivot table - that is, each row represents "student X taking course Y in semester Z":</p>
  
  <pre><code># Table course_students
  
  | student_id | semester_id | course_id |
  |------------|-------------|-----------|
  |     18     |     4       |    80     |
  |     18     |     8       |    64     |
  |     18     |     8       |    60     |
  </code></pre>
  
  <p>From this, I'd like to build a nested collection such that:</p>
  
  <ul>
  <li>Each student has a collection containing the semesters in which that student had at least one course;</li>
  <li>Each semester for a given student has a collection containing the courses the student took in that semester.</li>
  </ul>
  
  <p>So, for the above table, I'd like to call something like <code>Student::find(18)-&gt;with('coursesBySemester')</code> and get a collection that looks like:</p>
  
  <pre><code>{
      "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
                      }
                  }
              ]
          }
      ]
  }
  </code></pre>
  
  <h2>What I've tried</h2>
  
  <p>I can get most of the way there with the following relationship defined in my <code>Student</code> model:</p>
  
  <pre><code>/**
   * 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-&gt;belongsToMany('UserFrosting\Sprinkle\Btoms\Model\Semester', 'course_students')
      -&gt;with(['courses' =&gt; function ($query) {
          return $query-&gt;where('course_students.student_id', $this-&gt;id);
      }])
      -&gt;groupBy('semester_id');
  }
  </code></pre>
  
  <p>The <code>Semester</code> model has the following relationship defined:</p>
  
  <pre><code>/**
   * Lazily load a collection of courses that were taken in this semester.
   */
  public function courses()
  {
      return $this-&gt;belongsToMany('UserFrosting\Sprinkle\Btoms\Model\Course', 'course_students')-&gt;withPivot('student_id');
  }
  </code></pre>
  
  <p>The problem is that when I call <code>with('courses')</code> in my <code>coursesBySemester</code> relationship, it retrieves <em>all</em> courses that <em>any</em> student took in that semester.  I only want the courses that the <em>parent student</em> took in that semester.  </p>
  
  <p>As you can see I tried to constrain that relationship by using <code>where('course_students.student_id', $this-&gt;id)</code>, but <code>$this-&gt;id</code> does not actually have any value set in the context of the relationship.  I've also tried the <code>wherePivot</code> method, but again, I don't know how to dynamically set that constraint based on the <code>id</code> of the parent <code>Student</code> model.</p>
  
  <p>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.</p>