Laravel中如何在关联查询的原生SQL中传递变量？

当然可以实现变量传递，而且绝对不要直接拼接变量到原生SQL中，避免SQL注入风险。推荐两种安全的实现方式：

方法一：DB::raw参数绑定 #

Laravel的DB::raw()支持接收第二个参数作为绑定的参数数组，用占位符?替换硬编码的1即可：

$current_user_id = Auth::user()->id;
$query = DB::table('users as u')
    ->selectRaw('
        ROW_NUMBER() OVER (ORDER BY u.id) AS No,
        u.id,
        u.username,
        l.level AS Rank
    ')
    ->leftjoin(DB::raw('(
        SELECT 
            u.id as ts_user_id,
            u.username as ts_username, 
            u.email as ts_email, 
            l.downline_user_id AS ts_downline_user_id ,
            outer_u.username AS ts_downline_username,
            sum(o.pay_amount) AS ts_team_sales
        FROM 
            users as u
        INNER JOIN user_levels AS l
            ON l.upline_user_id = u.id
        INNER JOIN users as outer_u
            ON outer_u.id = l.downline_user_id
        LEFT JOIN user_levels as downline_level
            ON l.downline_user_id =  downline_level.upline_user_id
        INNER JOIN users as outerdownline__u
            ON outerdownline__u.id = downline_level.downline_user_id
        LEFT JOIN orders as o
            ON o.user_id = downline_level.downline_user_id
        WHERE o.status = 1 AND downline_level.level > 1 AND u.id = ?
        GROUP BY l.downline_user_id
    )RESULT_TEAM_SALES', [$current_user_id]) , 'RESULT_TEAM_SALES.ts_downline_user_id' , 'l.downline_user_id')
 ->where('u.id' , $current_user_id)
 ->groupBy('l.downline_user_id')
 ->get();

方法二：用selectSub重构子查询（更优雅） #

用Laravel查询构建器的selectSub()方法重构子查询，完全避免手写大段原生SQL，变量传递更自然：

$current_user_id = Auth::user()->id;

// 先构建子查询
$teamSalesSubquery = DB::table('users as u')
    ->select(
        'u.id as ts_user_id',
        'u.username as ts_username',
        'u.email as ts_email',
        'l.downline_user_id AS ts_downline_user_id',
        'outer_u.username AS ts_downline_username',
        DB::raw('sum(o.pay_amount) AS ts_team_sales')
    )
    ->join('user_levels AS l', 'l.upline_user_id', '=', 'u.id')
    ->join('users as outer_u', 'outer_u.id', '=', 'l.downline_user_id')
    ->leftJoin('user_levels as downline_level', 'l.downline_user_id', '=', 'downline_level.upline_user_id')
    ->join('users as outerdownline__u', 'outerdownline__u.id', '=', 'downline_level.downline_user_id')
    ->leftJoin('orders as o', 'o.user_id', '=', 'downline_level.downline_user_id')
    ->where('o.status', 1)
    ->where('downline_level.level', '>', 1)
    ->where('u.id', $current_user_id) // 直接传递变量
    ->groupBy('l.downline_user_id');

// 主查询关联子查询
$query = DB::table('users as u')
    ->selectRaw('
        ROW_NUMBER() OVER (ORDER BY u.id) AS No,
        u.id,
        u.username,
        l.level AS Rank
    ')
    ->leftJoinSub($teamSalesSubquery, 'RESULT_TEAM_SALES', function ($join) {
        $join->on('RESULT_TEAM_SALES.ts_downline_user_id', '=', 'l.downline_user_id');
    })
    ->where('u.id', $current_user_id)
    ->groupBy('l.downline_user_id')
    ->get();

注意事项 #

• 优先使用方法二，代码可读性更高，也更符合Laravel的ORM设计理念
• 无论哪种方法，都不要直接拼接变量到SQL字符串中，防止SQL注入攻击

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