title: Nested search/filtering with Laravel/Eloquent
body: <p>Let's say i have a table of houses, each of which has many rooms, each of which has many items in it. Each item has a color.</p>
  
  <pre><code>Houses
  + id
  + name
  
  Rooms
  + id
  + house_id (FK house.id)
  + name
  
  Items
  + id
  + room_id (FK room.id)
  + name
  + color
  </code></pre>
  
  <p>The corresponding model relationships ($this->hasMany()) has been set up. </p>
  
  <p>I then want to return a nested JSON object with all the <strong>red</strong> items in it, with the houses as the top level of the object. That is, the JSON object is structured as a list of houses, each of which has a list of rooms, each of which has a list of items. </p>
  
  <p>To be clear: If a house has no rooms with a red item in it, i don't want the house to be in the JSON object at all. If it only has some rooms with red items in it, i only want those rooms in the object. And finally of course, for the rooms with red items in it, i only want those red items to be in the object.</p>
  
  <p>How can i best do this using constraints like ->where(), or if not possible, how can i best use raw queries to do it?</p>
  
  <p>EDIT:
  I already did create a nested query using whereHas, something along the lines of:</p>
  
  <pre><code>House::whereHas('rooms', function ($rooms) {
      $rooms-&gt;whereHas('items', function ($items) {
          $items-&gt;where('color', 'red);
      });
  });
  </code></pre>
  
  <p>While this only gave me houses that had red items in it, it keeps the non-red items of those houses in the object as well.</p>