Laravel多用户统计查询问题：复用单用户逻辑返回空数据

问题重现 #

单个用户的统计查询可以正常返回数据：

return $user->statistics()
            ->selectRaw('created_at as date, MIN(level) as min_level, max(level) as max_level, SUM(level_change) as sum_level_change')
            ->whereDate('created_at', '>=', now()->subDays(7))
            ->groupByRaw('DAY(date)')
            ->latest()
            ->get();

返回结果示例：

[{"date": "2022-09-05 17:23:22","min_level": 2748,"max_level": 2750,"sum_level_change": "3"},...]

但查询房间内多个用户的统计数据时，statistics字段返回空数组：

return $room->users()
            ->with('statistics', function ($query) {
                $query->selectRaw('created_at as date, MIN(level) as min_level, max(level) as max_level, SUM(level_change) as sum_level_change');
                $query->whereDate('created_at', '>=', now()->subDays(7));
                $query->groupByRaw('DAY(date)');
            })
            ->limit(25)
            ->get();

返回结果示例：

[{"id": 1,"room_id": 65,..."statistics": []},...]

问题原因 #

1. 缺失关联外键：使用with进行关联查询时，selectRaw中没有包含user_id（统计模型与用户模型的关联外键），Laravel无法将统计数据与对应的用户匹配，因此返回空数组。
2. 分组维度错误：仅按DAY(date)分组会导致不同用户的同日期统计被合并，且无法关联到单个用户。

解决方案 #

修正关联查询，返回每个用户的统计数据 #

在统计查询的selectRaw中加入user_id，并调整分组维度同时包含user_id和DAY(date)，添加排序逻辑：

return $room->users()
    ->with(['statistics' => function ($query) {
        $query->selectRaw('user_id, created_at as date, MIN(level) as min_level, MAX(level) as max_level, SUM(level_change) as sum_level_change')
            ->whereDate('created_at', '>=', now()->subDays(7))
            ->groupByRaw('user_id, DAY(date)')
            ->orderByRaw('SUM(level_change) DESC'); // 按sum_level_change降序排序统计数据
    }])
    ->limit(25)
    ->get();

按用户的总sum_level_change排序 #

如果需要将房间内的用户按总level_change之和排序，可以通过子查询计算用户的总变化量，再进行排序：

return $room->users()
    ->with(['statistics' => function ($query) {
        $query->selectRaw('user_id, created_at as date, MIN(level) as min_level, MAX(level) as max_level, SUM(level_change) as sum_level_change')
            ->whereDate('created_at', '>=', now()->subDays(7))
            ->groupByRaw('user_id, DAY(date)')
            ->orderByRaw('SUM(level_change) DESC');
    }])
    // 子查询计算每个用户的总level_change
    ->selectRaw('users.*, (SELECT SUM(level_change) FROM statistics WHERE statistics.user_id = users.id AND DATE(created_at) >= ?) AS total_sum_level_change', [now()->subDays(7)->format('Y-m-d')])
    ->orderBy('total_sum_level_change', 'DESC')
    ->limit(25)
    ->get();

内容的提问来源于stack exchange，提问作者kasanar64