php Laravel OrderBy 关系计数

Laravel OrderBy relationship count

提问by Joe Torraca

I'm trying to get the most popular hackathons which requires ordering by the respective hackathon's partipants->count(). Sorry if that's a little difficult to understand.

我正在尝试获得最流行的黑客马拉松，这需要通过相应的黑客马拉松的partipants->count(). 对不起，如果这有点难以理解。

I have a database with the following format:

我有一个格式如下的数据库：

hackathons
    id
    name
    ...

hackathon_user
    hackathon_id
    user_id

users
    id
    name

The Hackathonmodel is:

该Hackathon模型是：

class Hackathon extends \Eloquent {
    protected $fillable = ['name', 'begins', 'ends', 'description'];

    protected $table = 'hackathons';

    public function owner()
    {
        return $this->belongsToMany('User', 'hackathon_owner');
    }

    public function participants()
    {
        return $this->belongsToMany('User');
    }

    public function type()
    {
        return $this->belongsToMany('Type');
    }
}

And HackathonParticipantis defined as:

并HackathonParticipant定义为：

class HackathonParticipant extends \Eloquent {

    protected $fillable = ['hackathon_id', 'user_id'];

    protected $table = 'hackathon_user';

    public function user()
    {
        return $this->belongsTo('User', 'user_id');
    }

    public function hackathon()
    {
        return $this->belongsTo('Hackathon', 'hackathon_id');
    }
}

I've tried Hackathon::orderBy(HackathonParticipant::find($this->id)->count(), 'DESC')->take(5)->get());but I feel like I made a big mistake (possibly the $this->id), because it doesn't work at all.

我试过了，Hackathon::orderBy(HackathonParticipant::find($this->id)->count(), 'DESC')->take(5)->get());但我觉得我犯了一个大错误（可能是 $this->id），因为它根本不起作用。

How would I go about trying to get the most popular hackathons which is based on the highest number of related hackathonParticipants?

我将如何尝试获得基于最多相关黑客马拉松参与者数量的最受欢迎的黑客马拉松？

采纳答案by user1669496

Edit: If using Laravel 5.2 or greater, use kJamesy's answer. It will likely perform a bit better because it's not going to need to load up all the participants and hackathons into memory, just the paginated hackathons and the count of participants for those hackathons.

编辑：如果使用 Laravel 5.2 或更高版本，请使用 kJamesy 的答案。它可能会表现得更好一些，因为它不需要将所有参与者和黑客马拉松加载到内存中，只需将分页的黑客马拉松和这些黑客马拉松的参与者数量加载到内存中。

You should be able to use the Collection's sortBy()and count()methods to do this fairly easily.

您应该能够很容易地使用Collection'ssortBy()和count()方法来做到这一点。

$hackathons = Hackathon::with('participants')->get()->sortBy(function($hackathon)
{
    return $hackathon->participants->count();
});

回答by kJamesy

This works for me in Laravel 5.3, using your example:

这在 Laravel 5.3 中对我有用，使用您的示例：

Hackathon::withCount('participants')->orderBy('participants_count', 'desc')->paginate(10); 

This way it is ordered on the query and the pagination works nicely.

这样它在查询中排序并且分页效果很好。

回答by tisuchi

Another approach can be by using withCount()method.

另一种方法可以是使用withCount()方法。

Hackathon::withCount('participants')
        ->orderBy('participants_count', 'desc')
        ->paginate(50);

Ref: https://laravel.com/docs/5.5/eloquent-relationships#querying-relations

参考：https: //laravel.com/docs/5.5/eloquent-relationships#querying-relations

回答by Johnyz

I had similar issue and using sortBy() is not suitable because of pagination, exactly as Sabrina Gelbart commented in previous solution. So I used db raw, here's simplified query:

我有类似的问题，由于分页，使用 sortBy() 不合适，正如 Sabrina Gelbart 在之前的解决方案中评论的那样。所以我使用了 db raw，这里是一个简化的查询：

Tag::select( 
array(
    '*',
    DB::raw('(SELECT count(*) FROM link_tag WHERE tag_id = id) as count_links')) 
)->with('links')->orderBy('count_links','desc')->paginate(5);   

回答by Muhammed Tanr?verdi

You can also use join operator. As Sabrina said, you can not use sortby at the db level.

您还可以使用连接运算符。正如 Sabrina 所说，您不能在 db 级别使用 sortby。

$hackathons = Hackathon::leftJoin('hackathon_user','hackathon.id','=','hackathon_user.hackathon_id')
           ->selectRaw('hackathon.*, count(hackathon_user.hackathon_id) AS `count`')
           ->groupBy('hackathon.id')
           ->orderBy('count','DESC')
           ->paginate(5);

But this code takes all records from database. So you should paginate manually.

但是这段代码从数据库中获取所有记录。所以你应该手动分页。

       $hackathons = Hackathon::leftJoin('hackathon_user','hackathon.id','=','hackathon_user.hackathon_id')
           ->selectRaw('hackathon.*, count(hackathon_user.hackathon_id) AS `count`')
           ->groupBy('hackathon.id')
           ->orderBy('count','DESC')
           ->skip(0)->take(5)->get();

Referred from : https://stackoverflow.com/a/26384024/2186887

引用自：https: //stackoverflow.com/a/26384024/2186887