我正在使用 Laravel，我有一个简单的任务，就是找到过去 x 天内没有打扫过的所有房间。

我需要使用 Eloquent 和 eager loading，结果应该只包含带有子记录的结果，例如，如果 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
{

}

Controller

use App\Room;

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

虽然我可以获得Room和对应的maintenancelog记录，

1. Controller 代码似乎是多余的(has() 和 with())引用相同的方法...
2. 我也不太确定如何从建筑物开始，然后是房间等，以便我拥有所有(且只有)相关的建筑物->房间->维护日志，所有其他不包含在集合中。
3. Room 模型使用 whereRaw 似乎有点笨拙？这个逻辑应该在 maintenancelog 模型中吗？查询也很笨重
4. 是否有更简单或更好的方法来完成我想做的事情？

最佳答案

试试这个

模型

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天内没有维护日志的房间

虽然我不确定如何做嵌套作用域

关于php - Laravel Eloquent 多重关系找到小于 x 天的最大日期，使用预加载，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/32804324/