Laravel eloquent sort query

Question

I'm trying to sort the users from my users table by the combined value of their distance in the activities table. The following code returns something like this.

I'm trying to figure out how to sort this data by the sum_distance value. Also I can't seem to figure out how to display just the sum_distance data as $item->sum_distance doesn't return anything. (because it's nested inside somewhere I guess)

User model

protected $fillable = ['strava_id', 'first_name', 'last_name', 'sex', 'avatar', 'email', 'token'];

public function sumDistance()
{
    return $this->hasMany('App\Activity')
        ->selectRaw('user_id, sum(distance) as sum_distance')
        ->groupBy('user_id')
        ->orderBy('sum_distance', 'desc');
}

Activity model

protected $fillable = ['activityId', 'user_id', 'name', 'distance', 'moving_time', 'start_date'];

Controller

public function index(Strava $strava)
{

    $list = User::with('sumDistance')->get();

    return view('leaderboard', compact('list'));

}

View

<ul>
    @foreach($list as $item)
        <li>{{ $item }}</li><br>
    @endforeach
</ul>

[laravel eloquent sort by relationship](https://stackoverflow.com/questions/40837690/laravel-eloquent-sort-by-relationship) — ljubadr, Oct 24 '17 at 16:14

apokryfos · Answer 1 · 2017-10-24T16:43:09.383

0

You can't order the user results by a related model as part of the SQL query because eloquent gets the relationships in a separate query. You could of course do a join but that will defeat the purpose of using the ORM. Here's what you can do:

public function sumDistance()
{
    return $this->hasMany('App\Activity')
        ->selectRaw('user_id, sum(distance) as sum_distance')
        ->groupBy('user_id');
}

Then sort after you've gotten the result:

public function index(Strava $strava)
{

    $list = User::with('sumDistance')->get()->sort(function ($a,$b) {
           return $a->sumDistance->sum_distance <=> $b->sumDistance->sum_distance; 
    });

    return view('leaderboard', compact('list'));

}

edited Oct 24 '17 at 16:43

answered Oct 24 '17 at 16:28

apokryfos

38,771
9
70
114

I see where you're coming from. yet the ->sortBy("sumDistance.sum_distance") doesn't seem to change the order of the results at all. – Robbe R Oct 24 '17 at 16:37
@RobbeR try the update if you're using PHP 7+ (if not then use a subtraction instead of `<=>`) – apokryfos Oct 24 '17 at 16:43

score 0 · Answer 2 · answered Oct 24 '17 at 16:57

If your eloquent query returns collection then you can use sortByDesc() method

public function index(Strava $strava)
{

    $list = User::with('sumDistance')->get();

    $list = $list->sortByDesc('sum_distance');

    return view('leaderboard', compact('list'));

}

Laravel eloquent sort query

2 Answers2