如何使用 Query Builder 或 Eloquent 添加 JOIN 和 CASE-WHEN？

Question

我有以下工作的原始 SQL 语句，我发现使用 Laravel/Query Builder/Eloquent 将其转换为 PHP 非常困难。

SELECT aa.filepath, 

       aa.filename, 

       inst.instrument, 

       rl.raga, 

       ks.keysig, 

       aa.id    AS AAID, 

       likes.id AS LikesID 

FROM   (audioassets aa) 

       INNER JOIN instruments inst 

               ON aa.instrument_id = inst.id 

       INNER JOIN keysigs ks 

               ON aa.keysig_id = ks.id 

       LEFT JOIN ragalist rl 

              ON aa.raga_id = rl.i 

       LEFT JOIN likes 

              ON aa.id = likes.audioassets_id 

                 AND CASE likes.user_id 

                       WHEN 1 THEN true 

                       ELSE false 

                     END 

WHERE  ks.keysig LIKE '%' 

       AND inst.instrument LIKE '%';

我的笔记：

我遇到的问题是试图让 CASE-WHEN 代码在 Laravel 中工作

“WHEN”后面的“1”是一个变量，所以现在只是为了测试

我要做的就是在“仪表板”上显示文件列表并显示当前登录的用户“喜欢”了哪些文件（没有其他人喜欢）

这是我的 Laravel Eloquent 代码：

$query = \App\Audioassets::where('instruments.instrument', 'LIKE', "%$ddinstselected%")

        ->where('keysigs.keysig', 'LIKE', "%$ddkeyselected%")

        ->join('instruments', 'audioassets.instrument_id', '=', 'instruments.id')

        ->leftjoin('ragalist', 'audioassets.raga_id', '=', 'ragalist.id')

        ->join('keysigs', 'audioassets.keysig_id', '=', 'keysigs.id')

        ->leftjoin('likes', 'likes.audioassets_id', '=', 'audioassets.id')

        ->where('likes.user_id', '=', $authuserid)

        ->select('audioassets.filepath', 'audioassets.filename', 

'instruments.instrument', 'ragalist.raga', 'keysigs.keysig', 'audioassets.id', 'likes.id as likesid');

这在 Laravel 中运行良好，但不包含 CASE-WHEN SQL，因此所有“喜欢”都会显示给所有用户。

任何帮助将不胜感激！

Answer 1

您可以尝试以下查询myFriendsData = DB::table('audioassets as aa')    ->where('ks.keysig', 'LIKE', '%')    ->join('instruments', 'audioassets.instrument_id', '=', 'instruments.id')    ->leftjoin('ragalist', 'audioassets.raga_id', '=', 'ragalist.id')    ->join('keysigs', 'audioassets.keysig_id', '=', 'keysigs.id')    ->join('likes ',audioassets.id = likes.audioassets_id, function() {})    ->whereRaw(        '(             CASE                  WHEN likes.user_id = 1 THEN true ELSE false                 END        )'    )    ->select('audioassets.filepath', 'audioassets.filename','instruments.instrument', 'ragalist.raga', 'keysigs.keysig', 'audioassets.id', 'likes.id as likesid');    ->get();

Answer 2

您可以使用提前连接子句和DB::raw()：<?php$query = \App\Audioassets::where('instruments.instrument', 'LIKE', "%$ddinstselected%")&nbsp; &nbsp; ->where('keysigs.keysig', 'LIKE', "%$ddkeyselected%")&nbsp; &nbsp; ->join('instruments', 'audioassets.instrument_id', '=', 'instruments.id')&nbsp; &nbsp; ->leftjoin('ragalist', 'audioassets.raga_id', '=', 'ragalist.id')&nbsp; &nbsp; ->join('keysigs', 'audioassets.keysig_id', '=', 'keysigs.id')&nbsp; &nbsp; // Use join clause.&nbsp; &nbsp; ->leftJoin('likes', function ($join) {&nbsp; &nbsp; &nbsp; &nbsp; $join->on('aa.id', '=', 'likes.audioassets_id')&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;// CASE WHEN by DB::raw()&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;->on(DB::raw('CASE likes.user_id WHEN 1 THEN true ELSE false'));&nbsp; &nbsp; )&nbsp; &nbsp; ->where('likes.user_id', '=', $authuserid)&nbsp; &nbsp; ->select(&nbsp; &nbsp; &nbsp; &nbsp; 'audioassets.filepath',&nbsp; &nbsp; &nbsp; &nbsp; 'audioassets.filename',&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; 'instruments.instrument',&nbsp; &nbsp; &nbsp; &nbsp; 'ragalist.raga',&nbsp; &nbsp; &nbsp; &nbsp; 'keysigs.keysig',&nbsp; &nbsp; &nbsp; &nbsp; 'audioassets.id',&nbsp; &nbsp; &nbsp; &nbsp; 'likes.id as likesid'&nbsp; &nbsp; );连接子句 API 参考：Illuminate\Database\Query\JoinClause | Laravel API

Answer 3

我没有回过头来回复这个帖子中的所有伟大帮助者，但答案是上面提供的两个答案的混合。这是完美运行的最终代码：$query = \App\Audioassets::where('instruments.instrument', 'LIKE', "%$ddinstselected%")->where('keysigs.keysig', 'LIKE', "%$ddkeyselected%")->leftJoin('likes', function ($join) use ($authuserid) {&nbsp; &nbsp; $join->on('audioassets.id', '=', 'likes.audioassets_id');&nbsp; &nbsp; $join->whereRaw(&nbsp; &nbsp; &nbsp; &nbsp; '(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; CASE&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; WHEN likes.user_id=? THEN true ELSE false&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; END&nbsp; &nbsp; &nbsp; &nbsp; )'&nbsp; &nbsp; &nbsp; &nbsp; , [$authuserid]);&nbsp; &nbsp; })->join('instruments', 'audioassets.instrument_id', '=', 'instruments.id')->leftjoin('ragalist', 'audioassets.raga_id', '=', 'ragalist.id')->join('keysigs', 'audioassets.keysig_id', '=', 'keysigs.id')->select('audioassets.filepath', 'audioassets.filename', 'instruments.instrument', 'ragalist.raga', 'keysigs.keysig', 'audioassets.id', 'likes.id as likesid');