eloquent - how to convert sql to query builder laravel

Question

Can anyone help me out to convert this SQL to query builder!

SELECT topwords.*, 
       mw.word AS my_word 
FROM   topwords 
       LEFT JOIN (SELECT DISTINCT words.word 
                  FROM   definition_word 
                         JOIN words 
                           ON words.id = definition_word.word_id 
                  WHERE  definition_word.user_id = $user) AS mw 
              ON topwords.word = mw.word 

I have a problem with how to use a subquery in leftjoin!

I tried something like this but it has error! See error as image

DB::table('topwords')
                    ->leftJoin(DB::raw("SELECT DISTINCT
                    words.word
                    FROM definition_word 
                    JOIN words ON words.id = definition_word.word_id
                    WHERE definition_word.user_id = $user as mw"),"topwords.word", "=", "mw.word" )
                    ->select(
                        "topwords.*",
                        "mw.word AS my_word"
                    )->orderBy('id','desc')->paginate(15);

question from:https://stackoverflow.com/questions/65902457/how-to-convert-sql-to-query-builder-laravel

Answer 1

you can use Join Sub query official document subquery-joins

$mw = DB::table('words')
        ->select('DISTINCT words.word')
        ->join('definition_word', function($join) use ($user)
        {
            $join->on('wordss.id', '=', 'definition_word.word_id')
                 ->where('definition_word.user_id',  $user);
        });
        
$topwords = DB::table('topwords')
        ->joinSub($mw, 'mw',function ($join) {
            $join->on('topwords.word', '=', 'mw.word');
        })
        ->select('topwords.*','mw.word AS my_word')
        ->orderBy('id','desc')
        ->paginate(15);