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

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!

I just found something in the user guide, apparently you need the with() method.

From the User-Guide:

By default only certain fields from the pivot table will be returned (the two id fields, and the timestamps). If your pivot table contains additional columns, you can fetch them too by using the with() method :

class User extends Eloquent {  public function roles()  {    return $this->has_many_and_belongs_to('Role', 'user_roles')->with('column');  }}

So you can then use something similar to this when defining your relationship:

$this->has_many_and_belongs_to('User')->with('playcount');

Example

I just used this to make sure it works...

class Song extends Eloquent {    function users()    {        return $this->has_many_and_belongs_to('User')->with('playcount');    }}class User extends Eloquent {    function songs()    {        return $this->has_many_and_belongs_to('Song')->with('playcount');    }}// My test methodclass TestOrm extends PHPUnit_Framework_TestCase {    public function testSomethingIsTrue()    {        foreach(User::find(3)->songs()->order_by('playcount')->get() as $song)            echo $song->name, ': ', $song->pivot->playcount, "\n";        echo "\n";        foreach(User::find(3)->songs()->order_by('playcount','desc')->get() as $song)            echo $song->name, ': ', $song->pivot->playcount, "\n";    }}

Output

Jingle Bells: 5Mary had a little lamb: 10Soft Kitty: 20The Catalyst: 100The Catalyst: 100Soft Kitty: 20Mary had a little lamb: 10Jingle Bells: 5

Note: It is no coincidence that without using order_by() the result appears sorted in ascending order by the playcount. I confirmed this through testing (as I do not know yet how to display queries in unit tests), but you should probably not rely on this behaviour.

Any method that's available in Fluent should also be available with Eloquent. Perhaps this is what you're looking for?

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