获取连接表上的最新值
我试图使用Eloquent来查找多个多有关系的最后一个表上的列的最大值。
给定下列表结构。
建筑+----+---------------+
| 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 |
+----+-----------+-------------+
maintenancelog
+----+-------------------+---------+---------------------+
| id | maintenance_value | room_id | timestamp |
+----+-------------------+---------+---------------------+
| 1 | Cleaned | 1 | 2015-09-06 00:54:59 |
| 2 | Cleaned | 1 | 2015-09-06 01:55:59 |
| 3 | Cleaned | 2 | 2015-09-06 02:56:59 |
| 4 | Cleaned | 2 | 2015-09-06 03:57:59 |
| 5 | Cleaned | 3 | 2015-09-06 04:58:59 |
| 6 | Cleaned | 3 | 2015-09-06 05:59:59 |
+----+-------------------+---------+---------------------+
我想看看是否有可能生成一个有说服力的语句,该语句将检索建筑物名称、房间名称和仅上次维护日志日期值。
下面的工作为我提供了所有值的集合。
$buildings = Building::with('rooms.maintenancelog')->get();
但是这个错误出来了,它看起来像是在试图调用建筑表上的max(maintenanclog .timestamp) .
$buildings = Building::with('rooms.maintenancelog')->max('maintenancelog.timestamp')->get();
错误返回:
.....(SQL: select max(`maintenancelog`.`timestamp`) as aggregate from `buildings`)
我是不是要求太多了,我应该使用基本的查询生成器
将以下关系添加到Rooms模型中…
public function latestMaintenance()
{
return $this->hasOne('AppMaintenancelog')->latest();
}
并将Eloquent语句更改为.
$buildings = Building::with('rooms.latestMaintenance')->get();