laravel 如何在 Eloquent ORM 中按多对多关系的数据透视表的字段排序

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

提问by duality_

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.

我已经使用 Eloquent ORM 有一段时间了，我对它非常了解，但是我不能执行以下操作，而在 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.

我有多对多歌曲的用户，中间表是 song_user （应该是这样）。根据播放次数判断，我想获得用户的热门歌曲。当然，播放次数存储在中间表中。

I can do it in Fluent:

我可以在 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:

简单。但我想在 Eloquent 中做到这一点，这当然行不通：

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

回答by BigBlueHat

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

不确定您是否打算迁移到 Laravel 4，但这里有一个 Eloquent 示例，用于按数据透视表字段进行排序：

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

withPivotis like the eloquent withand will add the play_countfield from the pivot table to the other keys it already includes. All the pivot table fields are prefixed with pivotin the results, so you can reference them directly in the orderBy.

withPivot就像 eloquentwith并将play_count数据透视表中的字段添加到它已经包含的其他键中。结果中的所有数据透视表字段都带有前缀pivot，因此您可以直接在orderBy.

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

我不知道它在 Laravel 3 中会是什么样子，但也许这会帮助你指明正确的方向。

Cheers!

干杯!

回答by dualed

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

我刚刚在用户指南中找到了一些东西，显然您需要该with()方法。

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

默认情况下，只会返回数据透视表中的某些字段（两个 id 字段和时间戳）。如果您的数据透视表包含其他列，您也可以使用 with() 方法获取它们：

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 ascendingorder 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.

注意：如果不使用order_by()结果按 的ascending顺序出现排序，这并非巧合playcount。我通过测试确认了这一点（因为我还不知道如何在单元测试中显示查询），但您可能不应该依赖这种行为。

回答by Travis Bennett

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

在 Fluent 中可用的任何方法也应该在 Eloquent 中可用。也许这就是你要找的？

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

回答by Collin James

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.

如果在连接表中有名为“order”的列，这可能不明确。如果是这样，您需要指定 ->order_by( 'article_tag.order' )。是的，您需要使用 ->with() 在结果集中获取该列。出于风格的考虑，我会将 with() 放在关系方法之外，而只返回原版关系对象。

回答by Josh LaMar

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

在你的 Eloquent 模型中，如果你包含表名，你可以链接 orderBy 列：

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