Laravel中合并不同列名用户数据并分页统计求和的方法

问题说明 #

需要从两张表中提取指定日期范围内的数据，将同一用户的统计数值合并相加后输出，但目前使用union/unionAll仅能合并两个结果集，无法合并相同用户的数据：例如ID为1的用户在A表中以publisher_id存储，统计字段ticket总和为10；在B表中以user_id存储，统计字段diamonds总和为20，期望合并后结果为30，但当前返回两条独立记录。同时需要保留paginate分页功能。

解决方案 #

核心是先通过unionAll合并两张表的聚合结果作为临时数据集，再对该数据集按user_id二次聚合求和，最后执行分页操作。具体步骤如下：

• 分别编写两张表的聚合查询，确保输出字段统一为user_id和coins
• 使用unionAll合并两个查询结果，作为子查询
• 对子查询结果按user_id分组，重新计算coins的总和
• 对二次聚合后的结果排序并执行分页

修改后的代码 #

public function index(Request $request)
{
    $begin_date = $request->get('begin_date') 
        ? date('Y-m-d 00:00:00', strtotime($request->get('begin_date'))) 
        : now()->startOfMonth()->toDateTimeString();
    $end_date = $request->get('end_date') 
        ? date('Y-m-d 23:59:59', strtotime($request->get('end_date'))) 
        : now()->endOfMonth()->toDateTimeString();

    $begin_time = strtotime($begin_date);
    $end_time = strtotime($end_date);

    // 表A的聚合查询
    $a_recharges = ARecharge::select('publisher_id as user_id', DB::raw('SUM(ticket) as coins'))
        ->whereBetween('created_at', [$begin_date, $end_date])
        ->groupBy('publisher_id');

    // 表B的聚合查询
    $b_recharges = BRecharge::select('user_id', DB::raw('SUM(diamonds) as coins'))
        ->whereBetween('create_time', [$begin_time, $end_time])
        ->groupBy('user_id');

    // 合并两个查询作为子查询，二次聚合后分页
    $recharges = DB::table($a_recharges->unionAll($b_recharges), 'temp')
        ->select('user_id', DB::raw('SUM(coins) as total_coins'))
        ->groupBy('user_id')
        ->orderByDesc('total_coins')
        ->paginate(10)
        ->appends($request->except('page'));

    return view('admin.publisherrecharges.index', compact('recharges'));
}

代码说明 #

• 用unionAll替代union，避免自动去重（防止丢失同一用户在单表内的多条聚合记录）
• 通过DB::table()将合并后的结果作为临时表temp，执行二次分组求和，确保同一用户的数值被合并
• 分页操作针对二次聚合后的最终结果，保证分页数据是合并后的正确数据

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