Ager loading and custom queries

Question

This question is a follow up of this and of this. But I’ll try to make them independent.

I have the following tables, weathers:

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

freguesias:

+----+-----------+
| id | name      |
+----+-----------+
|  1 | Cabeção   |
|  2 | Pavia     |
|  3 | Brotas    |
|  4 | Mora      |

Each parish belongs to a county:

County Model (county):

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

Freguesia Model:

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

public function current_weather() {
    return $this->hasOne(\App\Current_Weather::class, 'id_freg')->orderBy('id', 'DESC');
}
...

I’m doing an Eager loading this way:

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

What I want is the last weathers for each parish belonging to the County, that is, in other words the Current time, more recent of each parish, for this we can follow us by the weathers.id.

I I already have SQL that I would like to use to get the last lines inserted for a set of parishes:

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
     );

Note: This is a table with lots of data (more than 4 million lines, and more every hour that passes)

The solution hasOne(\App\Weather::class, ...) no Model Parish is not effective (very poor performance) because it does not limit in the query itself, ... LIMIT 1, in my opinion should do but does not.

Answer 1

I managed to solve thanks to a colleague in the SO EN:

In my Model Freguesia:

...
public function current_weather() {
    $f_key = 'id_freg';
    return $this->hasOne(\App\Weather::class, $f_key)
        ->where('id', function($query) use ($f_key) {
            $query->selectRaw('max(id)')
                ->from('weathers as w2')
                ->where('w2.' .$f_key, DB::raw('weathers.' .$f_key));
        });
}
....

So I can still:

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

My problem was to come across the fact that the hasOne relation does not limit the result in the query itself, extracts all the lines and then returns the first, that’s horrible for the performance when you have a few million rows in a table (Weathers in this case). REF: https://softonsofa.com/tweaking-eloquent-relations-how-to-get-n-related-models-per-parent/