php - Laravel Eloquent 地发现多个关系，使用预先加载来查找小于x天的最大日期

我用的是拉维，我有一个简单的任务，就是找到所有在过去x天内没有打扫过的房间。
我需要使用雄辩和急切的加载，结果应该只包括结果与儿童记录，例如，如果1号楼的所有房间已在'x'天内清理完毕，1号楼根本不应返回…
建筑

+----+---------------+
| id | building_name |
+----+---------------+
|  1 | Building 1    |
|  2 | Building 2    |
+----+---------------+

房间

+----+-----------+-------------+
| id | room_name | building_id |
+----+-----------+-------------+
|  1 | Room 1    |           1 |
|  2 | Room 2    |           1 |
|  3 | Room 3    |           2 |
+----+-----------+-------------+

维护日志

+----+-------------------+---------+---------------------+
| 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 |
+----+-------------------+---------+---------------------+

建筑模型

class Building extends Model
{
    public function rooms() {
        return $this->hasMany('App\Room');
    }
}

房间模型

class Room extends Model
{
    public function maintenancelog() {
        return $this->hasMany('App\Maintenancelog');
    }

    public function needCleaning() {
            return $this->hasMany('App\Maintenancelog')->whereRaw('id in (select                  id from (select id, max(created_at) as created_at from maintenancelog
group by id having created_at <  DATE_SUB(NOW(), INTERVAL 10 DAY)) x')
    }
}

维护日志模型

class Room extends Model
{

}

控制器

use App\Room;

$result = Room::has('needCleaning')->with('needCleaning')->get(); //this seems redundant?

虽然我可以得到房间和相应的维护日志记录，
控制器代码似乎冗余（都has()和with()）引用相同的方法…
我也不太确定如何从建筑开始，然后是房间等，这样我就拥有了所有（而且只有）相关的建筑->房间->维护日志，所有其他的都不包括在收藏中。
房间模型用whereraw看起来有点笨重？？这个逻辑应该改为maintenancelog模型吗？查询也很笨拙
有没有更简单或更好的方法来做我想做的事？

最佳答案

试试这个
模型

Class Building extends Eloquent
{
    public function Rooms()
    {
        return $this->hasMany('Room');
    }
}

Class Room extends Eloquent
{
    public function MaintenanceLogs()
    {
        return $this->hasMany('MaintenanceLog');
    }
}

代码

//set number of days before
$no_days = 5;

//get the start date where room was not cleaned
$start_date = date('Y-m-d', strtotime('-'.$no_days.' days', strtotime(date('Y-m-d'))));

//start eager loading
$query = Building::with(
                                array
                                (
                                    'Rooms' => function($qry)
                                    {
                                        $qry->with('MaintenanceLogs');
                                    }
                                )
                             );

//get all rooms that has
$query->whereHas('Rooms', function($qry) use ($start_date)
{
   //exclude room that has  maintenance log within this period date
   $qry->whereDoesntHave('MaintenanceLogs', function($q) use ($start_date)
   {
        $q->where('created_at', '>=', $start_date);
    });
});

$data = $query->get();

它将在5天内寻找没有维修记录的房间
但我不知道如何做嵌套作用域