我有以下有效的原始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 = AppAudioassets::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,因此所有用户都会显示所有"喜欢"。
任何帮助将不胜感激!
你可以试试下面的查询
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();
您可以使用高级连接子句并DB::raw()
:
<?php
$query = AppAudioassets::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')
// Use join clause.
->leftJoin('likes', function ($join) {
$join->on('aa.id', '=', 'likes.audioassets_id')
// CASE WHEN by DB::raw()
->on(DB::raw('CASE likes.user_id WHEN 1 THEN true ELSE false'));
)
->where('likes.user_id', '=', $authuserid)
->select(
'audioassets.filepath',
'audioassets.filename',
'instruments.instrument',
'ragalist.raga',
'keysigs.keysig',
'audioassets.id',
'likes.id as likesid'
);
加入子句 API 参考:阐明\数据库\查询\加入子句 |拉拉维尔原料药
感谢您的所有帮助! 我没有回过头来回应这个线程中的所有好帮手,但答案是上面提供的两个答案的混合。这是完美运行的最终代码:
$query = AppAudioassets::where('instruments.instrument', 'LIKE', "%$ddinstselected%")
->where('keysigs.keysig', 'LIKE', "%$ddkeyselected%")
->leftJoin('likes', function ($join) use ($authuserid) {
$join->on('audioassets.id', '=', 'likes.audioassets_id');
$join->whereRaw(
'(
CASE
WHEN likes.user_id=? THEN true ELSE false
END
)'
, [$authuserid]);
})
->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');