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

I have no setup to try it out, but have you tried something like `$songs = $usr->songs()->pivot()->order_by('play_count','desc')->songs();`? Question is if you can "get back" to `songs` after the pivot table, since it does not seem to be a normal model. — dualed, Jan 14 '13 at 01:20
Well you can always make the pivot table a *model* and work through that. You'd only have to set up additional foreign keys so that the entries are correctly removed when their respective parent is. — dualed, Jan 15 '13 at 11:27
I am currently doing that, but using this approach, you lose the automatic filtering of user-only songs ($user->songs()) and you have to remember to manually do it (`SongUser::where("user_id", "=", $user->id)->etc...`). — duality_, Jan 15 '13 at 13:05
Why, this is what relations are for, instead of one many-to-many you make two one-to-many relations with the pivot table. You'd just add something like `$this->has_many('song_user')` — dualed, Jan 15 '13 at 19:20

score 52 · Accepted Answer · edited Jul 24 '13 at 05:15

52

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!

edited Jul 24 '13 at 05:15

answered Jun 12 '13 at 12:35

BigBlueHat

2,355
25
30

3

I didn't know I could stick the orderBy in the model like that. You deserve at least +1 for that! – Relaxing In Cyprus Mar 12 '14 at 10:41
1

Would you please point out if pivot fields prefixing mentioned in the documentation? – Walid Ammar Jul 22 '15 at 08:52
Works in Laravel 5.2 without needing to prefix the pivot table fields – kerrin Sep 20 '16 at 05:59

dualed · Answer 2 · 2013-01-15T20:42:20.997

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.

score 2 · Answer 3 · answered Jan 13 '13 at 07:28

2

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

answered Jan 13 '13 at 07:28

Travis Bennett

21
1

No, I'm looking for a more eloquent way of doing it, see my example that doesn't work. – duality_ Jan 13 '13 at 11:46
1

The answer I posted does user Eloquent. It will return an array of Eloquent objects (songs in this case) belonging to a user and ordered via play_count field (from the join table). This could also be done directly form the user object whose songs you're trying to find $user->songs()->order_by('play_count', 'asc')->get(). Your failed example doesn't make any sense: eager loading the song_user pivot table isn't the same as creating a join and in this case, there's really no reason for you to be eager loading. A simple join should accomplish what you need. – Travis Bennett Jan 13 '13 at 15:59

score 0 · Answer 4 · answered Mar 30 '13 at 15:09

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.

score 0 · Answer 5 · answered Sep 26 '17 at 14:34

0

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

answered Sep 26 '17 at 14:34

Josh LaMar

199
3
8

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

5 Answers5

Example

Output

Linked