请求将指定Raw SQL转换为Laravel QueryBuilder并修正尝试代码

问题描述 #

需将以下原生SQL查询转换为Laravel QueryBuilder，但自行编写的代码无法正常运行，现附上原始SQL及尝试的QueryBuilder代码：

原始原生SQL代码 #

DB::select("SELECT
  u.id as user_id,
-- users.email as email,
   COALESCE(fr.score_total, 0) + COALESCE (fqr.score_total, 0) as score_total
  FROM users u
  LEFT JOIN (
    SELECT
      fr.user_id,
      SUM(fr.pts_won) as score_total
    FROM
      forecast_results fr
    WHERE
       platform_id = ".$platform->id."
       AND forecast_fixture_id in ({$fixtureIdsRawQuery})
    GROUP BY
      fr.user_id
    ) fr ON (fr.user_id = u.id)
  LEFT JOIN (
    SELECT
      fqr.user_id,
      SUM(fqr.pts_won) as score_total
    FROM
      forecast_quiz_results fqr
     WHERE
       platform_id = ".$platform->id."
       AND question_id in ({$questionIdsRawQuery})
    GROUP BY
      fqr.user_id
    ) fqr ON (fqr.user_id = u.id) WHERE platform_id = ".$platform->id.";")

自行尝试的QueryBuilder代码 #

DB::table('users')
    ->select('users.id as user_id', 'forecast_results.pts_won',DB::raw('SUM(COALESCE(forecast_results.pts_won, 0))'))
    ->leftJoin('forecast_results', 'users.id', '=', 'forecast_results.user_id')
    ->groupBy('users.id','forecast_results.pts_won')
    ->where('users.platform_id','=',1)
    ->simplePaginate(30);

问题排查与正确转换代码 #

现有代码的问题 #

1. 未处理原SQL中双表带子查询关联的核心逻辑，仅直接关联forecast_results，忽略了forecast_quiz_results的积分统计
2. 分组逻辑错误：原SQL按用户ID分组统计子查询积分，你的代码额外将forecast_results.pts_won加入分组，导致统计结果被拆分
3. 未实现原SQL中两个子查询积分相加的逻辑，也未完整保留COALESCE空值替换处理

正确的QueryBuilder代码 #

$platformId = $platform->id;
$fixtureIds = []; // 替换为你的fixture ID数组，例如[1,2,3]
$questionIds = []; // 替换为你的question ID数组，例如[4,5,6]

$result = DB::table('users as u')
    ->select(
        'u.id as user_id',
        DB::raw('COALESCE(fr.score_total, 0) + COALESCE(fqr.score_total, 0) as score_total')
    )
    ->leftJoin(
        DB::raw('(SELECT user_id, SUM(pts_won) as score_total FROM forecast_results WHERE platform_id = ? AND forecast_fixture_id IN ('.implode(',', array_fill(0, count($fixtureIds), '?')).') GROUP BY user_id) as fr'),
        'fr.user_id', '=', 'u.id',
        [$platformId, ...$fixtureIds]
    )
    ->leftJoin(
        DB::raw('(SELECT user_id, SUM(pts_won) as score_total FROM forecast_quiz_results WHERE platform_id = ? AND question_id IN ('.implode(',', array_fill(0, count($questionIds), '?')).') GROUP BY user_id) as fqr'),
        'fqr.user_id', '=', 'u.id',
        [$platformId, ...$questionIds]
    )
    ->where('u.platform_id', $platformId)
    ->simplePaginate(30);

代码说明 #

• 用DB::raw()包裹子查询，完全还原原SQL的关联逻辑
• 使用参数绑定替代字符串拼接，规避原原生SQL存在的注入风险
• 完整保留COALESCE空值处理，确保无数据时积分显示为0
• 实现两个子查询统计积分的相加逻辑，与原SQL结果一致
• 保留simplePaginate分页逻辑，符合需求

内容的提问来源于stack exchange，提问作者B.Samuel