Laravel 4-排序数据 [英] Laravel 4 - Sorting Data

问题描述

我正在Laravel 4中建立一个网站.我的数据库中有一个名为Games的表，其中包含游戏列表.默认情况下，我将这些游戏排序为:

I am building a website in Laravel 4. I have a table in my DB called Games which contains a list of games. By default, I am sorting these Games with:

$games = Game::orderBy('title', 'ASC')->paginate(20);

用户可以选择他们当前正在玩的游戏.这些记录存储在名为Players的表中，该表存储game_id和user_id.

Users can select which Games they are currently playing. These records are stored in a table called Players which stores game_id and user_id.

我创建了几个相关模型:

A couple of relevant models I have created:

class Game extends Eloquent {

    public function players()
    {
        return $this->hasMany('Player');
    }

}

class User extends Eloquent {

    public function players()
    {
        return $this->hasMany('Player');
    }

}

class Player extends Eloquent {

    public function game()
    {
        return $this->belongsTo('Game');
    }

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

}

我想做的是，能够按最受欢迎(最多玩家)对我的游戏进行排序，以便可以用这种方式查看游戏列表.我已经尝试了以下方法，但是它只是暗中的刺伤，因为我对PHP框架和Laravel还是很陌生.

What I would like to do is, be able to sort my Games by most popular (most total players) so that the Games list can be viewed in this way. I have tried the following, but it was nothing more than a stab in the dark as I'm pretty new to PHP frameworks and Laravel.

$games = Game::orderBy(count($this->players), 'DESC')->paginate(20);

显然cou​​nt($ this-> players)是错误的，但是我有点坚持.

Obviously the count($this->players) is wrong, but I'm kinda stuck on this one.

谢谢.希望一切都有道理！

Thanks. Hopefully that all makes sense!

:)

推荐答案

您需要先JOIN表，然后手动SELECT COUNT(player.id)，GROUP BY (game.id)和ORDER BY COUNT(player.id).

You need to JOIN the tables manually then SELECT COUNT(player.id), GROUP BY (game.id) and ORDER BY COUNT(player.id).

您的最终查询应如下所示:

Your final query should look like this:

SELECT game.*, COUNT(player.id) AS players
FROM game
LEFT JOIN player
    ON player.game_id = game.id
GROUP BY player.id
ORDER BY players

使用Laravel查询构建器生成此代码非常简单:

Generating this with Laravel query builder is pretty simple:

DB::table('game')
    ->leftJoin('player', 'player.game_id', '=', 'game.id')
    ->groupBy('player.id')
    ->orderBy(DB::raw('COUNT(player.id)'))
    ->select('game.*')
    ->get();

请注意，我们不能将COUNT别名为players，也不能使用模型(无法访问JOIN).

Note that we cannot alias the COUNT to players and we cannot use models (no way to JOIN).

这篇关于Laravel 4-排序数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！