eager loading and custom queries

Question

eager loading and custom queries

Navigation

#1 by (0 votes)

0

This question is a follow up this and 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');
}
...

Parish 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 that belongs to County , that is, in other words the current time, most recent of each parish, so we can follow us through weathers.id .

I have I already have SQL that I would like to use to go search 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)

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

php mysql laravel laravel-eloquent laravel-5

asked by anonymous 19.04.2018 / 19:46

1 answer

Bank modeling with classes with relationship from n to n Blank line template Elixir

score 0 · Answer 1

I was able to solve thanks to a colleague in SO EN:

In my Model Parish:

...
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 get the same:

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

My problem was that the hasOne relationship does not limit the result in the query itself, it extracts all rows and then returns the first, this is horrible for performance when you have a few million rows in a table (weathers in this case). REF: link