本文详解如何使用 laravel query builder 正确重构含多层 left join 子查询的原生 sql，重点解决 coalesce 汇总、动态条件绑定及表别名冲突等常见难点，并提供可直接运行的安全示例。
本文详解如何使用 laravel query builder 正确重构含多层 left join 子查询的原生 sql，重点解决 coalesce 汇总、动态条件绑定及表别名冲突等常见难点，并提供可直接运行的安全示例。
在 Laravel 中，将含子查询（尤其是聚合 + LEFT JOIN）的原生 SQL 转换为 Query Builder 并非不支持，而是需善用 DB::raw() 与子查询表达式。你提供的原始 SQL 包含两个关键子查询：分别对 forecast_results 和 forecast_quiz_results 按用户汇总 pts_won，再通过 COALESCE 合并得分——这无法通过链式 join() 直接实现，必须显式构造子查询作为“虚拟表”参与 JOIN。
✅ 正确做法是：将每个子查询封装为 DB::raw("( ... ) AS alias")，并在 leftJoin() 中引用该表达式，同时注意以下要点：
-
主表别名统一：原始 SQL 使用
users u，Laravel 默认表名为users，但子查询 JOIN 时需确保关联字段指向明确（如fr.user_id = users.id）； -
参数安全绑定：严禁字符串拼接
$platform->id或$fixtureIdsRawQuery，应使用预处理占位符?配合DB::raw()的第二个参数（数组绑定）； -
字段选择精简：避免
SELECT *或冗余字段；主查询只需users.id和计算字段score_total； -
GROUP BY 逻辑保留在子查询内：主查询无需
groupBy，因聚合已在子查询中完成。
以下是完整、安全、可复用的 Laravel Query Builder 实现：
use Illuminate\Support\Facades\DB;
// 假设 $platform 和 $fixtureIds、$questionIds 已定义（推荐传入整型数组）
$fixtureIds = [1, 2, 3]; // 示例：从请求或服务层获取
$questionIds = [101, 102];
// 构建第一个子查询：forecast_results 汇总
$frSub = DB::raw("(
SELECT
fr.user_id,
SUM(fr.pts_won) AS score_total
FROM forecast_results fr
WHERE fr.platform_id = ?
AND fr.forecast_fixture_id IN ('" . implode("','", $fixtureIds) . "')
GROUP BY fr.user_id
) AS fr");
// 构建第二个子查询：forecast_quiz_results 汇总
$fqrSub = DB::raw("(
SELECT
fqr.user_id,
SUM(fqr.pts_won) AS score_total
FROM forecast_quiz_results fqr
WHERE fqr.platform_id = ?
AND fqr.question_id IN ('" . implode("','", $questionIds) . "')
GROUP BY fqr.user_id
) AS fqr");
// 主查询：LEFT JOIN 子查询 + COALESCE 计算
$results = 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($frSub, 'fr.user_id', '=', 'u.id')
->leftJoin($fqrSub, 'fqr.user_id', '=', 'u.id')
->where('u.platform_id', '=', $platform->id)
->get();
⚠️ 重要注意事项：
-
SQL 注入防护：示例中
IN子句使用了implode字符串拼接，仅适用于已知可信的整型 ID 数组；若数据来源不可控，务必改用whereIn()动态绑定（需借助DB::raw+ 闭包或临时表，较复杂，推荐先校验输入）； -
空值处理：
COALESCE(..., 0)确保任一子查询无匹配时仍返回 0，避免NULL + INT = NULL； -
性能提示：对
forecast_fixture_id和question_id字段建立复合索引（如(platform_id, forecast_fixture_id)）可显著提升子查询速度； -
调试技巧：调用
toSql()查看生成语句（如DB::table(...)->toSql()），对比原生 SQL 结构是否一致。
总结：Laravel Query Builder 完全支持复杂子查询 JOIN，核心在于用 DB::raw() 将子查询声明为“命名子表”，再以标准 leftJoin() 关联。摒弃字符串拼接，坚持参数绑定与结构化分层，即可写出既安全又易维护的查询逻辑。
