laravel - How to sort by a field of the pivot table of a many-to-many relationship in Eloquent ORM

Question

I have been using Eloquent ORM for some time now and I know it quite well, but I can't do the following, while it's very easy to do in Fluent.

I have users with a many-to-many songs, intermediate table being song_user (like it should be). I'd like to get the top songs of a user, judging by the play count. Of course, play count is stored in the intermediate table.

I can do it in Fluent:

$songs = DB::table('songs')
    ->join('song_user', 'songs.id', '=', 'song_user.song_id')
    ->where('song_user.user_id', '=', $user->id)
    ->orderBy("song_user.play_count", "desc")
    ->get();

Easy. But I want to do it in Eloquent, which of course doesn't work:

$songs = Song::
    with(array("song_user" => function($query) use ($user) {
        $query->where("user_id", "=", $user->id)->orderBy("play_count", "desc");
    }))

Answer 1

Not sure if you plan to move to Laravel 4, but here's an Eloquent example for sorting by a pivot tables fields:

public function songs() {
  return $this
    ->belongsToMany('Song')
    ->withPivot('play_count')
    ->orderBy('pivot_play_count', 'desc');
}

withPivot is like the eloquent with and will add the play_count field from the pivot table to the other keys it already includes. All the pivot table fields are prefixed with pivot in the results, so you can reference them directly in the orderBy.

I've no idea what it would look like in Laravel 3, but perhaps this will help point you in the right direction.

Cheers!