How to pass a variable in join query in Laravel-CodePudding

I have a variable that I would like to pass in a joined RAW query. How can I do that ?

$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=1 **//HOW to pass the $current_user_id variable here**
            GROUP BY l.downline_user_id
        )RESULT_TEAM_SALES') , 'RESULT_TEAM_SALES.ts_downline_user_id' , 'l.downline_user_id')
     ->where('u.id' , $current_user_id)
     ->groupBy('l.downline_user_id')
     ->get()

I would like to replace Where u.id = $current_user_id . Is passing the variable possible ?

Please advise.

Thank you.

CodePudding user response：

Do something like

DB::table('users')
    ->join('contacts', function ($join) {
        $join->on('users.column', '=', $yourVariable);
    })
    ->get();

https://laravel.com/docs/9.x/queries

CodePudding user response：

So, this is how I solved it. I made a sub query

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

$teamSales= DB::table('users as u')
->selectRaw('
u.id,
u.username as ts_username,
l.downline_user_id AS ts_downline_user_id ,
outer_u.username  AS ts_downline_username,
sum(o.pay_amount) AS ts_team_sales
')
->join('user_levels as l' , 'u.id' , '=' , 'l.upline_user_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')
->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')

;

Then used leftJoinSub method to left join the sub query

  $query = DB::table('users as u')
   ->selectRaw('
   ROW_NUMBER() OVER (ORDER BY u.id) AS No,
   u.id,
   u.username,
   team_sales.ts_team_sales
   ')
    ->join('user_levels as l' , 'l.upline_user_id' , '=' , 'u.id')
   ->leftJoinSub($teamSales , 'team_sales' , function($join) {
        $join->on('l.downline_user_id', '=', 'team_sales.ts_downline_user_id');
   })
   ->where('u.id' , $current_user_id)
   ->groupBy('l.downline_user_id')
   ->get();