title: Laravel Nested Eager Loading with Constraints
body: <p>I have a Question Eloquent Model, a Course Eloquent Model, a University Eloquent Model. A One to Many relationship exists between the University and the Course. A Many to Many relationship exists between the Question and the Course. The Three models are shown below:</p>
  
  <p>Question Model</p>
  
  <pre><code>namespace App;
  use Illuminate\Database\Eloquent\Model;
  class Question extends Model
  {
      /**
       * The database table that the Model uses
       * @var string
       */
      protected $table = "questions";
  
      /**
       * The fields that are mass assignable
       * @var array
       */
      protected $fillable = ['title','body','images','quality_score','deactivated','creator_id','reviewer_id'];
  
  
      /**
       * Images is stored as serialized json.
       * So we cast it to a PHP array.
       * See: http://laravel.com/docs/5.1/eloquent-mutators#attribute-casting
       */
      protected $casts = [
          'images' =&gt; 'array',
      ];
  
      public function courses(){
          return $this-&gt;belongsToMany('App\Course');
      }
  }
  </code></pre>
  
  <p>Course Model</p>
  
  <pre><code>namespace App;
  
  use Illuminate\Database\Eloquent\Model;
  
  class Course extends Model
  {
      /**
       * The database table used by the model
       * @var string
       */
      protected $table  = "courses";
  
      /**
       * The fields that can be mass assigned
       * @var array
       */
      protected $fillable = ['name', 'instructor', 'acronym', 'university_id', 'creator_id', 'reviewer_id'];
  
      /**
       * There exists a many to one relationship between the Course and User
       * This user is the creator of the course
       *
       * @method void
       *
       */
      public function creator(){
          return $this-&gt;hasOne('App\User','creator_id');
      }
  
      /**
       * There exists a many to one relationship between the Course and User
       * This user is the reviewer of the course
       * The reviewer of the Course will always be an admin
       * If an Admin is the creator, then the reviewer is also the same admin
       *
       * @method void
       */
  
      public function reviewer(){
          return $this-&gt;hasOne('App\User','reviewer_id');
      }
  
      /**
       * There exists a one to many relationship between the University and the Course
       * This university is where the course is held
       * Courses may float i.e. not be associated to any university
       *
       * @method void
       */
      public function university(){
          return $this-&gt;belongsTo('App\University');
      }
  
      /**
       * This method is an accessor. It automatically changes the acronym to be all capitals
       * regardless of how it is stored in the database.
       * See: http://laravel.com/docs/5.1/eloquent-mutators#accessors-and-mutators
       * @param $value (String from Database)
       * @return string (Capitalized String)
       */
      public function getAcronymAttribute($value){
          return strtoupper($value);
      }
  }
  </code></pre>
  
  <p>University Model</p>
  
  <pre><code>namespace App;
  
  use Illuminate\Database\Eloquent\Model;
  
  class University extends Model
  {
      /**
       * The database table used by the model
       * @var string
       */
      protected $table = "universities";
  
      /**
       * The fields that can be mass assigned
       * name = Name of the University (Example: University of Illinois at Urbana Champaign)
       * acronym = Acronym of the University (Example: UIUC)
       * creator_id = Id of User that created the University
       * reviewer_id = Id of User that reviewed and approved the University
       *
       * Universities will not be displayed to users without admin role unless they have been reviewed.
       *
       * @var array
       */
      protected $fillable = ['name','acronym','creator_id','reviewer_id'];
  
      /**
       * This method is an accessor. It automatically changes the acronym to be all capitals
       * regardless of how it is stored in the database.
       * See: http://laravel.com/docs/5.1/eloquent-mutators#accessors-and-mutators
       * @param $value (String from Database)
       * @return string (Capitalized String)
       */
      public function getAcronymAttribute($value){
          return strtoupper($value);
      }
  
  }
  </code></pre>
  
  <p>On my home page I am showing a list of questions and allow filters for course and university. The controller method is shown here:</p>
  
  <pre><code>public function getHome(Request $request){
  
          /**
           * Eager Load with Course and University
           */
          $questions = Question::with('courses.university')-&gt;get();
  
          /*
           * Filter Questions to remove unwanted entries based on course id
           */
          if($request-&gt;has('course_id') &amp;&amp; $request-&gt;input('course_id') != -1){
              $questions = $questions-&gt;filter(function($question) use ($request){
                 foreach($question-&gt;courses as $course){
                     if ($course-&gt;id == $request-&gt;input('course_id')){
                         return true;
                     }
                 }
              });
          }
  
          /*
           * Filter Questions to remove unwanted entries based on university id
           */
          if($request-&gt;has('university_id') &amp;&amp; $request-&gt;input('university_id') != -1){
              $questions = $questions-&gt;filter(function($question) use ($request){
                  foreach($question-&gt;courses as $course){
                      if ($course-&gt;university-&gt;id == $request-&gt;input('university_id')){
                          return true;
                      }
                  }
              });
          }
  
          /*
           * Return the Welcome View with Pagination on the Questions Displayed
           * List of Courses and List of Universities
           */
          return view('welcome',[
              'questions' =&gt; $questions,
              'courses' =&gt; Course::all(),
              'universities' =&gt; University::all(),
              'selected_university_id' =&gt; $request-&gt;input('university_id',-1),
              'selected_course_id' =&gt; $request-&gt;input('course_id',-1)
          ]);
  
      }
  </code></pre>
  
  <p>What I am doing above is returning all the questions from the database and them combing them through to remove all the ones that don't match the filters. This is obviously quite inefficient. I want to use Nested Eager Loading with constraints except I am having a lot of trouble defining what the constraint would look like. Further, I want to use server side paginate to make the client experience better on lower speed internet connections.</p>
  
  <p>Here is one of my attempts:</p>
  
  <pre><code>$questions = Question::with(['courses.university' =&gt; function($query) use ($request){
              if($request-&gt;has('university_id') &amp;&amp; $request-&gt;input('university_id') != -1) {
                  $query-&gt;where('id', $request-&gt;input('university_id'));
              }
  
              if($request-&gt;has('course_id') &amp;&amp; $request-&gt;input('course_id') != -1){
                  $query-&gt;where('courses.id',$request-&gt;input('course_id'));
              }
          }])-&gt;paginate(10);
  </code></pre>
  
  <p>This works fine when I don't have any filters.</p>
  
  <p>When I do have a university_id defined, I get the error: Trying to get property of non-object (View: /var/www/testing.com/resources/views/welcome.blade.php)</p>
  
  <p>When I do have a course_id defined, I get the error: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'courses.id' in 'where clause' (SQL: select * from <code>universities</code> where <code>universities</code>.<code>id</code> in (1, 2) and <code>courses</code>.<code>id</code> = 1)</p>
  
  <p>I expected the error when I have course_id defined (because I took a blind stab at the first argument of the $query->where method.</p>
  
  <p>I am looking for help in defining the Nested Eager Loading Constraints.</p>