时间戳

首页 > 解决方案 > 如何在 Laravel Eloquent 的 When 子句中的 Where 闭包中添加括号

php - 如何在 Laravel Eloquent 的 When 子句中的 Where 闭包中添加括号

问题描述

我试图弄清楚如何使用 Laravel Eloquent 在 WHEN 条件内将生成外括号放在 OR 语句的组合上。


    $calls = DB::table('incoming_calls')
            ->leftJoin('scripts', 'SCRIPTID', '=', 'scripts.RECID')
            ->select('incoming_calls.RECID','INCOMING_DATE','INCOMING_TIME','SCRIPT_NAME AS MYINFO','MSG_FROM')
            ->when( (strlen(trim($searchterm))>0), function($query) use ($searchterm)
            {
                    return $query->where('MSG_FROM', 'LIKE', '%'.$searchterm.'%')
                    ->orWhere('MSG_TEL', 'LIKE', '%'.$searchterm.'%')
                    ->orWhere('MSG_MOBILE', 'LIKE', '%'.$searchterm.'%')
                    ->orWhere('MSG_COMPANY', 'LIKE', '%'.$searchterm.'%')
                    ->orWhere('incoming_calls.INFOTXT', 'LIKE', '%'.$searchterm.'%')
                    ->orWhere('MSG_ADDRESS', 'LIKE', '%'.$searchterm.'%')
                    ->orWhere('MSG_EMAIL', 'LIKE', '%'.$searchterm.'%');
            })
            ->when($bid>0, function($query) use ($bid)
            {
                    return $query->where('incoming_calls.COMPANYID', "=", $bid);
            })

这将生成以下 SQL：

select [incoming_calls].[RECID], [INCOMING_DATE], [INCOMING_TIME], [SCRIPT_NAME] as [MYINFO], [MSG_FROM] from [incoming_calls] left join [scripts] on [SCRIPTID] = [scripts].[RECID] where [MSG_FROM] LIKE ? or [MSG_TEL] LIKE ? or [MSG_MOBILE] LIKE ? or [MSG_COMPANY] LIKE ? or [incoming_calls].[INFOTXT] LIKE ? or [MSG_ADDRESS] LIKE ? or [MSG_EMAIL] LIKE ? and [incoming_calls].[COMPANYID] = ? order by [INCOMING_DATE] desc, [INCOMING_TIME] desc

现在我需要的是 OR 子句周围的括号：

select [incoming_calls].[RECID], [INCOMING_DATE], [INCOMING_TIME], [SCRIPT_NAME] as [MYINFO], [MSG_FROM] from [incoming_calls] left join [scripts] on [SCRIPTID] = [scripts].[RECID] where ( [MSG_FROM] LIKE ? or [MSG_TEL] LIKE ? or [MSG_MOBILE] LIKE ? or [MSG_COMPANY] LIKE ? or [incoming_calls].[INFOTXT] LIKE ? or [MSG_ADDRESS] LIKE ? or [MSG_EMAIL] LIKE ?) and [incoming_calls].[COMPANYID] = ? order by [INCOMING_DATE] desc, [INCOMING_TIME] desc

我了解如何独立进行闭包，并且可以使用 Raw SQL 编写它，但我想弄清楚如何在 ->when 条件下使用 Eloquent 来实现。有人设法做到这一点吗？

标签： phplaraveleloquent

解决方案

您可以通过这样的参数分组来做到这一点：

$calls = DB::table('incoming_calls')
            ->leftJoin('scripts', 'SCRIPTID', '=', 'scripts.RECID')
            ->select('incoming_calls.RECID','INCOMING_DATE','INCOMING_TIME','SCRIPT_NAME AS MYINFO','MSG_FROM')
            ->when( (strlen(trim($searchterm))>0), function($query) use ($searchterm) {
                return $query->where(function ($query) {
                    $query->where('MSG_FROM', 'LIKE', '%' . $searchterm . '%')
                    ->orWhere('MSG_TEL', 'LIKE', '%' . $searchterm . '%')
                    ->orWhere('MSG_MOBILE', 'LIKE', '%' . $searchterm . '%')
                    ->orWhere('MSG_COMPANY', 'LIKE', '%' . $searchterm . '%')
                    ->orWhere('incoming_calls.INFOTXT', 'LIKE', '%' . $searchterm . '%')
                    ->orWhere('MSG_ADDRESS', 'LIKE', '%' . $searchterm . '%')
                    ->orWhere('MSG_EMAIL', 'LIKE', '%' . $searchterm . '%');
                })
            })
            ->when($bid>0, function($query) use ($bid)
            {
                return $query->where('incoming_calls.COMPANYID', "=", $bid);
            })

推荐阅读