2

I'm trying to use Eloquent to find the max value of a column on the last table of a multiple has-many relationship.

Given the following table structure.

Buildings

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

Rooms

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

I'd like to see if it's possible to generate an eloquent statement that would retrieve the building name, room name, and ONLY the LAST maintenance log date value.

The following works to give me a collection of ALL the values.

$buildings = Building::with('rooms.maintenancelog')->get();

but this errors out, and it looks like it's trying to call max(maintenancelog.timestamp) on the buildings table..

$buildings = Building::with('rooms.maintenancelog')->max('maintenancelog.timestamp')->get();

Error returned:

.....(SQL: select max(`maintenancelog`.`timestamp`) as aggregate from `buildings`)

Am I just asking too much from eloquent, and should I just use the basic query builder

dangel
  • 7,238
  • 7
  • 48
  • 74

1 Answers1

3

Add the following relationship to the Rooms model...

public function latestMaintenance()
{
    return $this->hasOne('App\Maintenancelog')->latest();
}

and change the Eloquent statement to..

$buildings = Building::with('rooms.latestMaintenance')->get();

referenced Getting just the latest value on a joined table with Eloquent

Community
  • 1
  • 1
dangel
  • 7,238
  • 7
  • 48
  • 74