Laravel Eloquent: Multiple Wheres with OrWhere

I have a search feature inside my app that will search a "broad" search term of several columns or a defined column as the user selects. I also have an outside where statement that is searching for the the company id (multi tenant app) and the assigned subcompany_id, both belonging to the user.

When I search, I'm getting all results and it's not using the company_id or subcompany_id relating to the user. I figured out that it's using the subcompany_id within the or where statement brackets rather it being outside the bracket.

Version: Laravel 5.6

$request->broad = Checkbox for broad search.

Query:

$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);
                });

This code returns the following query:

select count(*) as aggregate from `leads` where (`lead_name` LIKE '%tire%' or `lead_contact` LIKE '%tire%' or `lead_phone1` LIKE '%tire%' or `lead_phone2` LIKE '%tire%' or `lead_merchant_id` = 'tire' or `secondary_mid` = 'tire' or `lead_address_city` = 'tire' or `lead_address_state` = 'tire' or `lead_address_zip` LIKE '%tire%' and `lead_deleted` = '0' and `lead_duplicate` <> '1' and `lead_company_id` = '1' and `lead_subcompany_id` = '1') and `leads`.`lead_deleted_timestamp` is null

What it should be doing:

select count(*) as aggregate from `leads` where (`lead_name` LIKE '%tire%' or `lead_contact` LIKE '%tire%' or `lead_phone1` LIKE '%tire%' or `lead_phone2` LIKE '%tire%' or `lead_merchant_id` = 'tire' or `secondary_mid` = 'tire' or `lead_address_city` = 'tire' or `lead_address_state` = 'tire' or `lead_address_zip` LIKE '%tire%' and `lead_deleted` = '0' and `lead_duplicate` <> '1') and `lead_company_id` = '1' and `lead_subcompany_id` = '1' and `leads`.`lead_deleted_timestamp` is null`

How can I achieve this using Laravel Eloquent?

from Newest questions tagged laravel-5 - Stack Overflow https://ift.tt/2rb87hw
via IFTTT