Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.2k views
in Technique[技术] by (71.8m points)

eloquent - how to convert sql to query builder laravel

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

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

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);

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...