CODEWITHSUNDEEP
laraveleloquent
duality_
duality_

Reputation: 18756

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

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");
    }))

Upvotes: 35

Views: 27133

Answers (5)

Josh LaMar
Josh LaMar

Reputation: 209

In your Eloquent model you can chain the orderBy column if you include the table name:

return $this->belongsToMany('App\Post')->withTimestamps()->orderByDesc('posts.created_at');

Upvotes: 0

BigBlueHat
BigBlueHat

Reputation: 2365

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!

Upvotes: 53

Collin James
Collin James

Reputation: 9280

I have been doing this ( on several builds ) simply using the relationship method as you have. I often use an 'order' column in the pivot table and then do something like this.

$article->tags()->order_by( 'order')->get();

This may be ambiguous if you have columns named 'order' in the joining table. If so you would need to specify ->order_by( 'article_tag.order' ). And yes, you need to use ->with() to get that column in the result set. As just a matter of style I would leave the with() out of the relationship method and just return the vanilla relationship object instead.

Upvotes: 0

dualed
dualed

Reputation: 10502

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 method
class 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: 5
Mary had a little lamb: 10
Soft Kitty: 20
The Catalyst: 100

The Catalyst: 100
Soft Kitty: 20
Mary had a little lamb: 10
Jingle 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.

Upvotes: 5

Travis Bennett
Travis Bennett

Reputation: 21

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();

Upvotes: 2

Related Questions