如何将含子查询、联表、分组的SQL改写为Laravel DB查询并解决无返回问题

现有代码核心问题 #

• 直接将$id变量拼接进DB::raw语句，没有做参数绑定，存在SQL注入风险，同时如果$id值不符合预期会直接触发SQL语法错误，导致程序中断无输出，这是你执行dd无返回的主要原因。
• 手动拼写子查询的DB::raw写法不符合Laravel查询构造器规范，容易出现语法兼容性问题。
• Laravel默认开启MySQL严格模式，要求group by子句包含所有select中的非聚合字段，你的现有查询中select的s.uuid、s.name等字段没有出现在group by中，会触发SQL执行错误。

最优改写方案 #

你的需求是获取不重复的supplier_id+manufacturer_id组合，用DISTINCT比GROUP BY更贴合场景，改写后的代码如下：

$verifs = \DB::table('supplierverifs')
    // 自动做参数绑定，避免SQL注入和语法错误
    ->where('account_id', $id)
    // 关联supplier对应的公司表
    ->leftJoin('companies as s', 's.id', '=', 'supplier_id')
    // 关联manufacturer对应的公司表
    ->leftJoin('companies as m', 'm.id', '=', 'manufacturer_id')
    // 指定查询字段，同时对两个关联ID去重
    ->selectRaw(
        'DISTINCT supplier_id, manufacturer_id,
        s.uuid as supplier_uuid,
        m.uuid as manufacturer_uuid,
        s.name as supplier_name,
        m.name as manufacturer_name'
    )
    ->get();

如果需要保留GROUP BY写法，需把所有非聚合字段加入分组条件：

$verifs = \DB::table('supplierverifs')
    ->where('account_id', $id)
    ->leftJoin('companies as s', 's.id', '=', 'supplier_id')
    ->leftJoin('companies as m', 'm.id', '=', 'manufacturer_id')
    ->select(
        'supplier_id',
        'manufacturer_id',
        's.uuid as supplier_uuid',
        'm.uuid as manufacturer_uuid',
        's.name as supplier_name',
        'm.name as manufacturer_name'
    )
    // 严格模式下需要把所有select的非聚合字段加入group by
    ->groupBy('supplier_id', 'manufacturer_id', 'supplier_uuid', 'manufacturer_uuid', 'supplier_name', 'manufacturer_name')
    ->get();

问题排查方法 #

如果修改后仍有异常，可先打印生成的SQL语句和原生SQL对比，快速定位差异：

// 把get()换成toSql()即可打印最终生成的SQL语句
dd(
    \DB::table('supplierverifs')
    ->where('account_id', $id)
    ->leftJoin('companies as s', 's.id', '=', 'supplier_id')
    ->leftJoin('companies as m', 'm.id', '=', 'manufacturer_id')
    ->selectRaw(
        'DISTINCT supplier_id, manufacturer_id,
        s.uuid as supplier_uuid,
        m.uuid as manufacturer_uuid,
        s.name as supplier_name,
        m.name as manufacturer_name'
    )
    ->toSql()
);

你也可以在.env文件中将APP_DEBUG设置为true，开启调试模式后会直接显示具体的SQL错误信息。

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