order by 和 group by同时使用踩的坑

业务场景

file

按照最近的玩过的好友排序,并且获取到和他玩的输赢总次数,并分页

数据库字段

id user_id rival_id game_id game_type count_win count_draw count_lose count_all upd_time
主键 用户ID 对手ID 游戏ID 游戏类型 赢的次数 和的次数 输得次数 总共局数 更新时间

错误的方案

原始数据

这里我们看到,我查询除了所有5号用户的对战记录

SELECT * FROM game_fight_data WHERE user_id=5;

file

错误的ORM查询

// 获取json数据
$jsonData = $this->reqJson();
// 获取每次取出条目数
$limit = isset($jsonData['limit']) ? $jsonData['limit'] : 12;
// 获取页码
$page = isset($jsonData['page']) ? $jsonData['page'] : 1;
$lists = $gameFightDataRepository->m()->where('user_id', $id)
    ->select(DB::raw("user_id,rival_id,
            SUM(count_win) AS count_win,
            SUM(count_draw) AS count_draw,
            SUM(count_lose) AS count_lose,
            SUM(count_all) AS count_all"))
    ->groupBy('rival_id')
    ->take(($page - 1) * $limit)
    ->orderBy('upd_time','DESC')
    ->paginate($limit);

产生的SQL

select count(*) as aggregate from `game_fight_data` where `user_id` = 5 group by `rival_id`;

select user_id,rival_id,SUM(count_win) AS count_win,SUM(count_draw) AS count_draw,SUM(count_lose) AS count_lose,SUM(count_all) AS count_all from `game_fight_data` where `user_id` = 5 group by `rival_id` order by `upd_time` desc limit 12 offset 0;
{
    "current_page": 1,
    "data": [
        {
            "id": 87,
            "user_id": "5",
            "rival_id": "92",
            "count_win": "3",
            "count_draw": "0",
            "count_lose": "6",
            "count_all": "9"
        },
        {
            "id": 70,
            "user_id": "5",
            "rival_id": "91",
            "count_win": "3",
            "count_draw": "0",
            "count_lose": "3",
            "count_all": "6"
        },
        {
            "id": 15,
            "user_id": "5",
            "rival_id": "6",
            "count_win": "13",
            "count_draw": "0",
            "count_lose": "18",
            "count_all": "31"
        },
        {
            "id": 28,
            "user_id": "5",
            "rival_id": "8",
            "count_win": "19",
            "count_draw": "1",
            "count_lose": "21",
            "count_all": "41"
        },
        {
            "id": 55,
            "user_id": "5",
            "rival_id": "20",
            "count_win": "2",
            "count_draw": "0",
            "count_lose": "7",
            "count_all": "9"
        },
        {
            "id": 8,
            "user_id": "5",
            "rival_id": "2",
            "count_win": "5",
            "count_draw": "0",
            "count_lose": "10",
            "count_all": "15"
        }
    ],
    "first_page_url": "https://game.iluoy.com/api/v1.0/user/game/fightList?page=1",
    "from": 1,
    "last_page": 1,
    "last_page_url": "https://game.iluoy.com/api/v1.0/user/game/fightList?page=1",
    "next_page_url": null,
    "path": "https://game.iluoy.com/api/v1.0/user/game/fightList",
    "per_page": 12,
    "prev_page_url": null,
    "to": 6,
    "total": 6
}

数据分析

file

这里groupBy和orderBy连用了,所以orderBy是没有用的,自然只会渠道第一条数据,也就是说,我们这样做,分组且按照upd_time排序是方案是无效的

利用group_concat函数排序方案(但是我感觉这个不是最优方案)

ORM SQL语句

$allIds = $gameFightDataRepository->m()
            ->select(DB::raw("GROUP_CONCAT(id,'-',rival_id order by `upd_time` desc) AS ids"))
            ->where('user_id', $id)
            ->first();
{
    "ids": "89-92,110-20,87-92,108-92,106-20,9-2,80-91,77-91,76-91,73-91,71-91,70-91,57-20,55-20,47-8,46-8,43-8,41-8,40-6,29-8,28-8,21-6,20-6,15-6,13-2,12-2,8-2"
}

语句分析

按照所有的id和rival_id 并且按照upd_time排序,中间用"-"隔开,但是我觉得这里肯定可以直接orderBy,不过由于时间问题,没能研究,之后再研究

select GROUP_CONCAT(id,'-',rival_id order by `upd_time` desc) AS ids from `game_fight_data` where `user_id` = 5 limit 1;