title: Laravel eloquent multiple relationships to find max date less than x days, using eager loading
body: <p>I'm using Laravel, and I have a simple task of find all rooms that haven't been cleaned in the past x number of days.</p>
  
  <p>I need to use Eloquent and eager loading, and the result should ONLY include results with child records, for example if all the rooms in Building 1 have been cleaned in 'x' days, Building 1 should not be returned at all...</p>
  
  <p><strong>Buildings</strong></p>
  
  <pre><code>+----+---------------+
  | id | building_name |
  +----+---------------+
  |  1 | Building 1    |
  |  2 | Building 2    |
  +----+---------------+
  </code></pre>
  
  <p><strong>Rooms</strong></p>
  
  <pre><code>+----+-----------+-------------+
  | id | room_name | building_id |
  +----+-----------+-------------+
  |  1 | Room 1    |           1 |
  |  2 | Room 2    |           1 |
  |  3 | Room 3    |           2 |
  +----+-----------+-------------+
  </code></pre>
  
  <p><strong>maintenancelog</strong></p>
  
  <pre><code>+----+-------------------+---------+---------------------+
  | id | maintenance_value | room_id |      created_at     |
  +----+-------------------+---------+---------------------+
  |  1 | Cleaned           |       1 | 2015-09-01 00:54:59 |
  |  2 | Cleaned           |       1 | 2015-09-06 01:55:59 |
  |  3 | Cleaned           |       2 | 2015-09-02 02:56:59 |
  |  4 | Cleaned           |       2 | 2015-09-07 03:57:59 |
  |  5 | Cleaned           |       3 | 2015-09-03 04:58:59 |
  |  6 | Cleaned           |       3 | 2015-09-08 05:59:59 |
  +----+-------------------+---------+---------------------+
  </code></pre>
  
  <p><strong>Building Model</strong></p>
  
  <pre><code>class Building extends Model
  {
      public function rooms() {
          return $this-&gt;hasMany('App\Room');
      }
  }
  </code></pre>
  
  <p><strong>Room Model</strong></p>
  
  <pre><code>class Room extends Model
  {
      public function maintenancelog() {
          return $this-&gt;hasMany('App\Maintenancelog');
      }
  
      public function needCleaning() {
              return $this-&gt;hasMany('App\Maintenancelog')-&gt;whereRaw('id in (select                  id from (select id, max(created_at) as created_at from maintenancelog
  group by id having created_at &lt;  DATE_SUB(NOW(), INTERVAL 10 DAY)) x')
      }
  }
  </code></pre>
  
  <p><strong>maintenancelog Model</strong></p>
  
  <pre><code>class Room extends Model
  {
  
  }
  </code></pre>
  
  <p><strong>Controller</strong></p>
  
  <pre><code>use App\Room;
  
  $result = Room::has('needCleaning')-&gt;with('needCleaning')-&gt;get(); //this seems redundant?
  </code></pre>
  
  <p>While I can get the Room and corresponding maintenancelog records, </p>
  
  <ol>
  <li>the controller code seems redundant (both <code>has()</code> and <code>with()</code>) referencing the same method...</li>
  <li>I'm also not quite sure how to start off with Building and then rooms etc so that I have all (and only) the buildings->rooms->maintenancelogs that are relevant, all others are not included in the collection.</li>
  <li>The Room model seems a little clunky using whereRaw??  Should this logic be in the maintenancelog model instead?  the query is clunky as well</li>
  <li>Is there an easier or better way to do what I'm trying to do?</li>
  </ol>