php laravel eloquent 按关系排序

laravel eloquent sort by relationship

提问by Hitori

I have 3 models

我有3个模型

• User
• Channel
• Reply

• 用户
• 渠道
• 回复

model relations

模型关系

• userhave belongsToMany('App\Channel');
• channelhave hasMany('App\Reply', 'channel_id', 'id')->oldest();

• user有 belongsToMany('App\Channel');
• channel有 hasMany('App\Reply', 'channel_id', 'id')->oldest();

let's say i have 2 channels - channel-1 - channel-2

假设我有 2 个频道 - 频道 1 - 频道 2

channel-2has latest replies than channel-1

channel-2最新回复超过 channel-1

now, i want to order the user's channel by its channel's current reply. just like some chat application. how can i order the user's channel just like this?

现在，我想通过频道的当前回复对用户的频道进行排序。就像一些聊天应用程序。我怎样才能像这样订购用户的频道？

• channel-2
• channel-1

• 通道 2
• 通道 1

i already tried some codes. but nothing happen

我已经尝试了一些代码。但什么也没发生

// User Model
public function channels()
    {
        return $this->belongsToMany('App\Channel', 'channel_user')
                    ->withPivot('is_approved')
                    ->with(['replies'])
                    ->orderBy('replies.created_at'); // error

    }
// also
public function channels()
    {
        return $this->belongsToMany('App\Channel', 'channel_user')
                    ->withPivot('is_approved')
                    ->with(['replies' => function($qry) {
                        $qry->latest();
                    }]);
    }
// but i did not get the expected result

EDITalso, i tried this. yes i did get the expected result but it would not load all channel if there's no reply.

也编辑，我试过这个。是的，我确实得到了预期的结果，但如果没有回复，它不会加载所有频道。

public function channels()
{
    return $this->belongsToMany('App\Channel')
                ->withPivot('is_approved')
                ->join('replies', 'replies.channel_id', '=', 'channels.id')
                ->groupBy('replies.channel_id')
                ->orderBy('replies.created_at', 'ASC');
}

EDIT:

编辑：

回答by Steve.NayLinAung

According to my knowledge, eager loadwithmethod run 2nd query. That's why you can't achieve what you want with eager loadingwithmethod.

据我所知，急切加载with方法运行第二个查询。这就是为什么你不能用急切加载with方法实现你想要的。

I think use joinmethod in combination with relationship methodis the solution. The following solution is fully tested and work well.

我认为join结合使用方法和关系方法是解决方案。以下解决方案经过全面测试并且运行良好。

// In User Model
public function channels()
{
    return $this->belongsToMany('App\Channel', 'channel_user')
        ->withPivot('is_approved');
}

public function sortedChannels($orderBy)
{
    return $this->channels()
            ->join('replies', 'replies.channel_id', '=', 'channel.id')
            ->orderBy('replies.created_at', $orderBy)
            ->get();
}

Then you can call $user->sortedChannels('desc')to get the list of channelsorder by replies created_atattribute.

然后您可以调用$user->sortedChannels('desc')以通过回复属性获取频道列表created_at。

For condition like channels(which may or may not have replies), just use leftJoinmethod.

对于像频道这样的条件（可能有也可能没有回复），只需使用leftJoin方法。

public function sortedChannels($orderBy)
    {
        return $this->channels()
                ->leftJoin('replies', 'channel.id', '=', 'replies.channel_id')
                ->orderBy('replies.created_at', $orderBy)
                ->get();
    }

Edit:

编辑：

If you want to add groupBymethod to the query, you have to pay special attention to your orderByclause. Because in Sqlnature, Group Byclause run first before Order Byclause. See detail this problem at this stackoverflow question.

如果要向groupBy查询添加方法，则必须特别注意您的orderBy子句。因为在Sql性质中，Group By子句在Order By子句之前先运行。在此 stackoverflow 问题中查看此问题的详细信息。

So if you add groupBymethod, you have to use orderByRawmethod and should be implemented like the following.

所以如果你添加groupBy方法，你必须使用orderByRaw方法并且应该像下面那样实现。

return $this->channels()
                ->leftJoin('replies', 'channels.id', '=', 'replies.channel_id')
                ->groupBy(['channels.id'])
                ->orderByRaw('max(replies.created_at) desc')
                ->get();

回答by Max Flex

Firstly, you don't have to specify the name of the pivot table if you follow Laravel's naming conventionso your code looks a bit cleaner:

首先，如果您遵循Laravel 的命名约定，则不必指定数据透视表的名称，因此您的代码看起来更简洁：

public function channels()
{
    return $this->belongsToMany('App\Channel') ...

Secondly, you'd have to call joinexplicitly to achieve the result in one query:

其次，您必须join明确调用才能在一个查询中获得结果：

public function channels()
{
    return $this->belongsToMany(Channel::class) // a bit more clean
        ->withPivot('is_approved')
        ->leftJoin('replies', 'replies.channel_id', '=', 'channels.id') // channels.id
        ->groupBy('replies.channel_id')
        ->orderBy('replies.created_at', 'desc');
}

回答by Adam

Inside your channel class you need to create this hasOnerelation (you channel hasManyreplies, but it hasOnelatest reply):

在您的频道类中，您需要创建此hasOne关系（您频道hasMany回复，但它是hasOne最新回复）：

public function latestReply()
{
    return $this->hasOne(\App\Reply)->latest();
}

You can now get all channels ordered by latest reply like this:

您现在可以获得按最新回复订购的所有频道，如下所示：

Channel::with('latestReply')->get()->sortByDesc('latestReply.created_at');

To get all channels from the user ordered by latest reply you would need that method:

要获取按最新回复订购的用户的所有频道，您需要该方法：

public function getChannelsOrderdByLatestReply()
{
    return $this->channels()->with('latestReply')->get()->sortByDesc('latestReply.created_at');
}

where channels()is given by:

其中channels()由：

public function channels()
{
    return $this->belongsToMany('App\Channel');
}

回答by Gjaa

If you have a hasOne()relationship, you can sort all the records by doing:

如果您有hasOne()关系，您可以通过执行以下操作对所有记录进行排序：

$results = Channel::with('reply')
                   ->join('replies', 'channels.replay_id', '=', 'replies.id')
                   ->orderBy('replies.created_at', 'desc')
                   ->paginate(10);

This sorts all the channelsrecords by the newest replies (assuming you have only one reply per channel.) This is not your case, but someone may be looking for something like this (as I was.)

这会按channels最新回复对所有记录进行排序（假设您每个频道只有一个回复。）这不是您的情况，但有人可能正在寻找这样的东西（就像我一样。）