Translate sql raw query into eloquent relationship

This is a follow up question from this one.

County model:

...
public function freguesias() {
    return $this->hasMany(\App\Freguesia::class, 'id_county');
}
...

Now basically I have on my Freguesia Model:

...
public function county() {
    return $this->belongsTo(\App\County::class, 'id_county');
}

public function weathers() {
    return $this->hasMany(\App\Weather::class, 'id_freg');
}
...

And I am eager loading this relations from county, e.g:

$item = County::with(['freguesias' => function($q) {
    $q->with(['weathers']);
}])->select(['id'])->findOrfail(request()->id);

My main problem with this is that I have to collect (last inserted row in weathers table with id_freg = X) the current weather for each Freguesia that belongs to a specific County. where weathers.id_freg IN (X, Y, Z) ordered by id DESC

My weathers table looks like:

+--------+-------+---------+
| id     | temp  | id_freg |
+--------+-------+---------+
|    337 | 12.36 |       1 |
|   3556 | 11.46 |       2 |
|   6775 |  9.30 |       3 |
|  10210 |  8.55 |       1 |
|  13429 |  9.69 |       2 |

I have already the correct SQL for this:

select w.* 
from weathers w
where w.id_freg in (X, Y, ...) and
      w.id = (select max(w2.id) 
              from weathers w2 
              where w2.id_freg = w.id_freg
             );

But how can I implement this in the eager loading? Or is there other way through County Model maybe?

from Newest questions tagged laravel-5 - Stack Overflow https://ift.tt/2HcGzCC
via IFTTT