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?
