Laravel 5.1 creates a collection or associative array from the raw database (or simply from an array)

advertisements

given the following table structure, I want to return either an Eloquent collection or at least convert the raw DB result to an associative array for easier iteration.

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 |      created_at     |
+----+-------------------+---------+---------------------+
|  1 | Cleaned           |       1 | 2015-09-10 00:54:59 |
|  2 | Cleaned           |       1 | 2015-09-13 01:55:59 |
|  3 | Cleaned           |       2 | 2015-09-09 02:56:59 |
|  4 | Cleaned           |       2 | 2015-09-14 03:57:59 |
|  5 | Cleaned           |       3 | 2015-09-08 04:58:59 |
|  6 | Cleaned           |       3 | 2015-09-15 05:59:59 |
+----+-------------------+---------+---------------------+

Now using the following raw DB query..

$results = DB::select('
  select b.building_name,r.room_name,x.maxdate FROM buildings b
  join rooms r on r.building_id = b.id
  join (select room_id,max(created_at) as maxdate from maintenancelog group by room_id) x on x.room_id=room.id
  having x.maxdate < DATE_SUB(NOW(), INTERVAL 10 DAY)');

returns

Array
(
    [0] => stdClass Object
        (
            [building_name] => Building 1
            [room_name] => Room 1
            [maxdate] => 2015-09-13 01:55:59
        )

    [1] => stdClass Object
        (
            [building_name] => Building 1
            [room_name] => Room 2
            [maxdate] => 2015-09-14 03:57:59
        )

    [2] => stdClass Object
        (
            [building_name] => Building 2
            [room_name] => Room 3
            [maxdate] => 2015-09-15 05:59:59
        )

Is there a Laravel helper function that given an array, I can specify keys and grouping orders? sort of like, group by building_name,room_name so that it will return either a collection or associative array with "buildings" and "rooms" as keys to make iteration easier? I could build one, but it seems like a common task. (I did search)

I'm using Eloquent and have models made for the tables, but the query is too complicated for me to figure out how to turn it into an Eloquent statement. So even if there is a way to create the collection using the existing models?

(note I have figured out how to use relationships and query scopes to work backwards from maintenance log -> rooms -> buildings. but it puts buildings at the lowest level of the relationship, and I want it to be first)


in Building class:

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

in Room class:

public function maintenanceLog(){
    return $this->hasMany('App\MaintenanceLog');
}

in MaintenanceLog class:

public function scopeLongest($query){ //you can name it as you like ofc.
    return $query->orderBy('created_at','desc')->first();
}

Finally you can use:

foreach(Building::all() as $building){
    foreach($building->rooms() as $room){
        echo 'building: ' . $building->building_name . ' room: ' . $room->room_name . ' maxdate: ' . $room->maintenanceLog()->longest()->created_at;
    }
}