Laravel, sort result on field from relation table?

Question

I have a list with gamers and another table with game stats.

My list code is:

$gamers = Gamer::with(['lastGameStat' => function($query) {
    $query->orderBy('total_points', 'DESC');
}])->paginate(20);

relation:

public function lastGameStat() {
        return $this->hasOne(GameStat::class, 'gamer_id', 'id')->orderBy('created_at', 'DESC');
    }

in relation table I have field: total_points and with this code I thought it's possible to sort list of gamers by total_points $query->orderBy('total_points', 'DESC');

It doesn't work, can somebody give me an advice here how can I sort the result on a field from relation table?

Dan · Accepted Answer · 2020-03-29 17:03:30Z

I guess you'll need either another relation or custom scopes to fetch various game stats of a gamer.

Second relation

Gamer.php (your model)

class Gamer
{
    public function bestGameStat()
    {
        return $this
            ->hasOne(GameStat::class)
            ->orderBy('total_points', 'DESC');
    }
}

Custom scopes

Gamer.php

class Gamer
{
    public function gameStat()
    {
        return $this->hasOne(GameStat::class);
    }
}

GameStat.php

use Illuminate\Database\Eloquent\Builder;

class GameStat
{
    public function scopeBest(Builder $query)
    {
        return $query->orderBy('total_points', 'DESC');
    }
}

In your controller:

$gamersWithTheirLatestGameStatistic = Gamer::with(['gameStat' => function($query) {
    $query->latest();
}])->paginate(20);

$gamersWithTheirBestGameStatistic = Gamer::with(['gameStat' => function($query) {
    $query->best();
}])->paginate(20);

Be aware as this is untested code and might not work.

Collectives™ on Stack Overflow

Laravel, sort result on field from relation table?

1 Answer 1

Second relation

Custom scopes

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Second relation

Custom scopes

Comments

Your Answer

Sign up or log in

Post as a guest

Related