php - Laravel 5.1 查询生成器，带有 'in' 子句和多个嵌套 OR 查询

Question

我正在尝试将此 sql 转换为 php laravel 5.1 查询。

SELECT `videoId`, `videoTitle`FROM `tempdetails` WHERE `videoTitle` LIKE '%Ramsays Kitchen Nightmares%' 
or videoTitle in
(
videoTitle in 
(`videoTitle` LIKE '%season 1%' or `videoTitle` LIKE '%e01%' or `videoTitle` LIKE '%01x%')
or
videoTitle in
(`videoTitle` LIKE '%episode 1%' or `videoTitle` LIKE '%s01%' or `videoTitle` LIKE '%x01%') 
)

这是我尝试过的代码。

  $vd = DB::table('tempdetails');
               $vd ->where('videoTitle', 'like', '%'.$titl.'%')
               ->whereIn('videoTitle',function($query) {
                 $query->orwhere( 'videoTitle', 'like', '%season 1%')
                 ->orWhere( 'videoTitle', 'like', '%s01%')
                 ->orWhere( 'videoTitle', 'like', '%01x%');
               });
               $vd->whereIn('videoTitle',function($query) {
                 $query->orWhere( 'videoTitle', 'like', '%episode 1%')
                 ->orWhere( 'videoTitle', 'like', '%e01%')
               ->orWhere( 'videoTitle', 'like', '%x01%');
             });
            $vd->get();

但这给了我一个错误:

SQLSTATE[HY000]: General error: 1096 No tables used (SQL: select * from tempdetails where videoTitle like %Ramsay's Kitchen Nightmares% and videoTitle in (select * where videoTitle like %season 1% or videoTitle like %s01% or videoTitle like %01x%) and videoTitle in (select * where videoTitle like %episode 1% or videoTitle like %e01% or videoTitle like %x01%))

有人知道如何实现这一目标吗？

Answer 1

您可以使用whereRaw或DB::select()。当我有无法直观翻译的复杂语句时，除非我确实需要 Eloquent ，否则我更喜欢运行原始语句。

此外，我不确定您的数据是什么样的以及您想要实现的目标，但可能有一种方法可以重写您的查询，以便它可以更好地适应 Eloquent 约束。

whereRaw() 的示例

$wherelikes = "videoTitle in
(
videoTitle in 
(`videoTitle` LIKE '%season 1%' or `videoTitle` LIKE '%e01%' or `videoTitle` LIKE '%01x%')
or
videoTitle in
(`videoTitle` LIKE '%episode 1%' or `videoTitle` LIKE '%s01%' or `videoTitle` LIKE '%x01%') 
)"
$vd = DB::table('tempdetails')
          ->whereRaw("`videoTitle` LIKE '%Ramsays Kitchen Nightmares%'")
          ->orWhere(function($query) use ($whereLikes) {
            $query->whereRaw($whereLikes)
           })->get();

DB::select() 示例

$statement = "SELECT `videoId`, `videoTitle`FROM `tempdetails` WHERE `videoTitle` LIKE '%Ramsays Kitchen Nightmares%' 
or videoTitle in
(
videoTitle in 
(`videoTitle` LIKE '%season 1%' or `videoTitle` LIKE '%e01%' or `videoTitle` LIKE '%01x%')
or
videoTitle in
(`videoTitle` LIKE '%episode 1%' or `videoTitle` LIKE '%s01%' or `videoTitle` LIKE '%x01%') 
)"
$vd = DB::select(DB::raw($statement));