CODEWITHSUNDEEP
phplaraveleloquentlaravel-5.5
Hula Hula
Hula Hula

Reputation: 573

orderBy in hasOne relation doesn't work

I have a table (weathers) with several thousands rows, 90000 +- at the moment, each one belonging to one location.

This table can have multiple rows belonging to one location, but I still want just one, the last one for a given location.

My model Location have this relation defined as:

...
public function last_weather() {
    return $this->hasOne(\App\Weather::class, 'id_location')->orderBy('weathers.id', 'DESC');
}
...

And on my controller I'm retrieving the last_weather like:

...
Location::with(['last_weather'])->findOrfail(1);
...

The strange thing is that this worked until I have 45000+- rows in the weather table, I have 3200 locations, and the last records for each location that are returned are on 40000+- rows (between id 40000 and 43000 +-, of the weathers table)

I have checked my DB and I have each location updated on the 80000's, but the relation are returning the data from the 40000's. This is not even the first or the last weather for each location.

Upvotes: 0

Views: 84

Answers (2)

Cooper
Cooper

Reputation: 140

You can do this in your Location model

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

public function lastWeather()
{
   return $this->weathers()->latest()->first();
}

Then in your controller

$location = Location::findOrfail(1);

then you can access the last weather like this

$location->lastWeather();

UPDATE

Or you can adjust how you eager load weathers

$location = Location::with([
        'weathers' => function($query) {
            $query->orderBy('id', 'DESC')->first();
        },
    ])
    ->findOrfail(1);

Upvotes: 3

miknik
miknik

Reputation: 5941

Order by will return all rows, to only return a single row for each matching condition you need to use Group by

I never used Laravel, but looking at your code I'm guessing your query should look like this:

return $this->hasOne(\App\Weather::class, 'id_location')->groupBy('weathers.id', 'DESC');

Upvotes: 1

Related Questions