Laravel雄辩：多重where与OrWhere[重复]

Question

我有一个搜索功能在我的应用程序，将搜索一个“广泛的”搜索词的几个列或一个定义的列作为用户选择。我还有一个outserwhere语句，用于搜索公司id（多租户应用程序）和分配的subcompany_id，它们都属于用户。

当我搜索时，我得到了所有的结果，它没有使用与用户相关的company_id或subcompany_id。我发现它在或where语句括号内使用subcompany_id，而不是在括号外。

$Leads = Lead::with('Bank')
                ->with('BankBranch')
                ->with('Account')
                ->with('LeadStatus')
                ->with('SalesRep')
                ->when($HideAccounts == True, function ($HideAccounts) {
                    $HideAccounts->where('lead_merchant_id', '');
                })
                ->when(isset($request), function ($CustomSearch) use ($request) {
                    $CustomSearch->when(!empty($request->broad), function ($BroadSearch) use ($request) {
                        $BroadSearch->where('lead_name', 'LIKE', '%' . $request->broad . '%')
                            ->orWhere('lead_contact', 'LIKE', '%' . $request->broad . '%')
                            ->orWhere('lead_phone1', 'LIKE', '%' . $request->broad . '%')
                            ->orWhere('lead_phone2', 'LIKE', '%' . $request->broad . '%')
                            ->orWhere('lead_merchant_id', $request->broad)
                            ->orWhere('secondary_mid', $request->broad)
                            ->orWhere('lead_address_city', $request->broad)
                            ->orWhere('lead_address_state', $request->broad)
                            ->orWhere('lead_address_zip', 'LIKE', '%' . $request->broad . '%');
                    });

                    if(!empty($request->company_name)) {
                        $CustomSearch->where('lead_name', 'LIKE', '%' . $request->company_name . '%');
                    }

                    if(!empty($request->lead_contact)) {
                        $CustomSearch->where('lead_contact', 'LIKE', '%' . $request->lead_contact . '%');
                    }

                    if(!empty($request->address_city)) {
                        $CustomSearch->where('lead_address_city', $request->address_city);
                    }

                    if(!empty($request->address_state)) {
                        $CustomSearch->where('lead_address_state', $request->address_state);
                    }

                    if (!empty($request->sic_code)) {
                        $CustomSearch->where('lead_sic_code', 'LIKE', '%' . $request->sic_code . '%');
                    }

                    if (!empty($request->lead_leadstatus_id)) {
                        $CustomSearch->where('lead_leadstatus_id', $request->lead_leadstatus_id);
                    }

                    if(!empty($request->address_zip)) {
                        $CustomSearch->where('lead_address_zip', 'LIKE', '%' . $request->address_zip . '%');
                    }

                    if(!empty($request->phone)) {
                        $CustomSearch->where('lead_phone1', $request->phone);
                        $CustomSearch->orWhere('lead_phone2', $request->phone);
                    }

                    if (!empty($request->lead_referral_user_id)) {
                        $CustomSearch->where('lead_referral_user_id', $request->lead_referral_user_id);
                    }

                    if (!empty($request->lead_sales_representative_id)) {
                        $CustomSearch->where('lead_sales_representative_id', $request->lead_sales_representative_id);
                    }

                    if (!empty($request->lead_referral_bank_id)) {
                        $CustomSearch->where('lead_referral_bank_id', $request->lead_referral_bank_id);
                    }

                    if (!empty($request->lead_referral_bankbranch_id)) {
                        $CustomSearch->where('lead_referral_bankbranch_id', $request->lead_referral_bankbranch_id);
                    }

                    if (!empty($request->lead_created)) {
                        $LeadCreated = Carbon::createFromFormat('M d, Y', $request->lead_created)->startOfDay();

                        if (!empty($LeadCreated)) {
                            $CustomSearch->where('lead_created_timestamp', '>=', $LeadCreated);
                        }
                    }

                    if (!empty($request->lead_created_end)) {
                        try {
                            $LeadCreatedEnd = Carbon::createFromFormat('M d, Y', $request->lead_created_end)->startOfDay();
                        } catch (\Exception $e) {
                            $LeadCreatedEnd = NULL;
                        }

                        if (!empty($LeadCreatedEnd)) {
                            $CustomSearch->where('lead_created_timestamp', '<=', $LeadCreatedEnd);
                        }
                    }

                    if (!empty($request->account_approval_start)) {
                        try {
                            $AccountApprovalStart = Carbon::createFromFormat('M d, Y', $request->account_approval_start)->startOfDay();
                        } catch (\Exception $e) {
                            $AccountApprovalStart = NULL;
                        }

                        if (!empty($AccountApprovalStart)) {
                            $CustomSearch->whereHas('Account', function ($Account) use ($AccountApprovalStart) {
                                $Account->where('account_created_timestamp', '>=', $AccountApprovalStart);
                            });
                        }
                    }

                    if (!empty($request->account_approval_end)) {
                        try {
                            $AccountApprovalEnd = Carbon::createFromFormat('M d, Y', $request->account_approval_end)->startOfDay();
                        } catch (\Exception $e) {
                            $AccountApprovalEnd = NULL;
                        }

                        if (!empty($AccountApprovalEnd)) {
                            $CustomSearch->whereHas('Account', function ($Account) use ($AccountApprovalEnd) {
                                $Account->where('account_created_timestamp', '<=', $AccountApprovalEnd);
                            });
                        }
                    }
                })
                ->where('lead_company_id', Auth::user()->user_company_id)
                ->when(Auth::user()->user_subcompany_id != NULL, function ($query) {
                    return $query->where('lead_subcompany_id', Auth::user()->user_subcompany_id);
                });

此代码返回以下查询：

从`leads`中选择count(*)作为聚合，其中(`lead_name`LIKE'%tire%'或`lead_contact`LIKE'%tire%'或`lead_contact`LIKE'%tire%'或`lead_phone1`LIKE'%tire%'或`lead_phone2`LIKE'%tire%'或`lead_merchant_id`='tire'或`lead_merchant_id`='tire'或`secondary_mid`='tire'或`lead_address_state`='tire'或.`lead_deleted_timestamp`为null

它应该做的是：

我如何使用Laravel Eloquent实现这一点？

Answer 1

如果您想在“$BroadSearch”部分周围加上括号，那么您需要将查询放在它自己的作用域中：

$BroadSearch->where(function($query) {
    $query->where('lead_name', 'LIKE', '%' . $request->broad . '%')
        ->orWhere('lead_contact', 'LIKE', '%' . $request->broad . '%')
        ->orWhere('lead_phone1', 'LIKE', '%' . $request->broad . '%')
        ->orWhere('lead_phone2', 'LIKE', '%' . $request->broad . '%')
        ->orWhere('lead_merchant_id', $request->broad)
        ->orWhere('secondary_mid', $request->broad)
        ->orWhere('lead_address_city', $request->broad)
        ->orWhere('lead_address_state', $request->broad)
        ->orWhere('lead_address_zip', 'LIKE', '%' . $request->broad . '%');
});