Laravel中如何多表查询并按日期分组生成日报表

需要从users和deposits两张表取数并按日期分组，生成包含日期、活跃用户数（当日注册用户数）、总存款（当日存款总额）的日报表。

已知信息：

• 表结构：
  • users：id、created_at 等字段
  • deposits：id、user_id、amount、created_at 字段
• 模型关联：User 拥有多个 Deposit，Deposit 属于 User
• 当前已实现单表分组查询，但无法完成多表联合查询，认为UNION、JOIN不适用，现有单表代码：

  Deposit::select(DB::raw("date(created_at) as date, SUM('amount') as total_deposit"))->groupBy('date')->orderBy('date')->get();
  

  User::select(DB::raw("date(created_at) as date, count('*') as active_player"))->groupBy('date')->orderBy('date')->get();
  

核心思路 #

先整合所有存在数据的日期（包含有用户注册或有存款的日期），再通过左连接关联两个表的统计结果，确保每个日期的两个指标都能正确展示，同时处理空值为0。

可行实现代码 #

方法一：基于DB门面的日期维度整合查询

// 构造所有存在数据的日期集合
$allDates = DB::table(
    DB::raw("(SELECT DATE(created_at) as date FROM users UNION SELECT DATE(created_at) as date FROM deposits) AS date_dim")
)->select('date')->orderBy('date');

// 关联用户统计和存款统计，生成日报表
$dailyReport = DB::table($allDates, 'd')
    ->leftJoin(
        DB::raw("(SELECT DATE(created_at) as date, COUNT(*) as active_player FROM users GROUP BY date) AS user_stats"),
        'd.date', '=', 'user_stats.date'
    )
    ->leftJoin(
        DB::raw("(SELECT DATE(created_at) as date, SUM(amount) as total_deposit FROM deposits GROUP BY date) AS deposit_stats"),
        'd.date', '=', 'deposit_stats.date'
    )
    ->select(
        'd.date',
        DB::raw("COALESCE(user_stats.active_player, 0) AS active_player"),
        DB::raw("COALESCE(deposit_stats.total_deposit, 0) AS total_deposit")
    )
    ->orderBy('d.date')
    ->get();

方法二：基于模型的子查询合并

如果倾向于使用Laravel模型来实现，可以通过子查询合并两个统计结果：

// 预定义用户注册统计子查询
$userStats = User::select(DB::raw("DATE(created_at) as date, COUNT(*) as active_player"))
    ->groupBy('date');

// 预定义存款统计子查询
$depositStats = Deposit::select(DB::raw("DATE(created_at) as date, SUM(amount) as total_deposit"))
    ->groupBy('date');

// 合并两个子查询，处理日期不重叠的情况
$dailyReport = DB::table($userStats, 'u')
    ->rightJoin($depositStats, 'u.date', '=', 'deposit_stats.date')
    ->union(
        DB::table($depositStats, 'p')
            ->rightJoin($userStats, 'p.date', '=', 'user_stats.date')
    )
    ->select(
        DB::raw("COALESCE(u.date, p.date) AS date"),
        DB::raw("COALESCE(u.active_player, 0) AS active_player"),
        DB::raw("COALESCE(p.total_deposit, 0) AS total_deposit")
    )
    ->groupBy('date')
    ->orderBy('date')
    ->get();

重要修正提示 #

原单表代码存在语法错误：

• SUM('amount') 和 count('*') 中的单引号需要去掉，应该写成 SUM(amount) 和 COUNT(*)，否则会将字段名当作字符串处理，导致统计结果错误。

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