laravel雄辩地按关系排序 [英] laravel eloquent sort by relationship

问题描述

我有3个模型

• 用户
• 频道
• 回复

模型关系

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

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

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

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

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

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

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

编辑 另外，我尝试了这个.是的，我确实得到了预期的结果，但是如果没有回复，它将无法加载所有频道.

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

推荐答案

据我所知，急于加载 with方法运行第二次查询.这就是为什么急切加载 with方法无法实现所需目标的原因.

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

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

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

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

然后，您可以调用$user->sortedChannels('desc')以通过回复created_at属性获得渠道的列表.

Then you can call $user->sortedChannels('desc') to get the list of channels order by replies created_at attribute.

对于诸如渠道之类的条件(可能有或没有回复)，只需使用leftJoin方法.

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

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

如果要向查询添加groupBy方法，则必须特别注意orderBy子句.因为具有 Sql 性质，所以Group By子句首先在Order By子句之前运行.请在此stackoverflow问题中详细了解此问题.

If you want to add groupBy method to the query, you have to pay special attention to your orderBy clause. Because in Sql nature, Group By clause run first before Order By clause. See detail this problem at this stackoverflow question.

因此，如果添加groupBy方法，则必须使用orderByRaw方法，并且应按以下方式实现.

So if you add groupBy method, you have to use orderByRaw method and should be implemented like the following.

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

这篇关于laravel雄辩地按关系排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！