I am building a query step by step:-
$query = $modelClass::with(array(
'employeeRegionalCenter',
'employeeMembershipCenter',
'employeeMembershipCenter.membershipCenterLanguage' => function($languageQuery) {
$languageQuery->where('language', App::getLocale());
},
'employeeLanguage' => function($languageQuery) {
$languageQuery->where('language', App::getLocale());
}))
->where(array(
'deleted_at' => NULL
));
if(auth()->guard('admin')->user()['type'] === Globals::ADMIN_USER) {
$query->where(function($orQuery) {
$orQuery->where('created_by', new ObjectId(auth()->guard('admin')->user()['_id']))
->orWhere('regional_center_id', new ObjectId(auth()->guard('admin')->user()['_id']));
});
}
Then based on the search key I submitted, I am again putting conditional filtering in the query:-
if(isset($request->searchKey) && !empty($request->searchKey) && $request->searchKey !== '')
{
$searchKey = $request->searchKey;
$query->where(function ($orQuery) use ($searchKey){
$orQuery->where('name', 'LIKE', '%' . $searchKey . '%')
->orWhere('mobile', 'LIKE', '%' . $searchKey . '%')
->orWhere('card_id', 'LIKE', '%' . $searchKey . '%')
->orWhereHas('employeeLanguage', function ($languageRelationQuery) use ($searchKey){
$languageRelationQuery->where(function($languageQueryEn) use($searchKey) {
$languageQueryEn->where('name', 'LIKE', '%' . $searchKey . '%')
->where('language', 'en');
})
->orWhere(function($olanguageQueryAr) use($searchKey) {
$olanguageQueryAr->where('name', 'LIKE', '%' . $searchKey . '%')
->where('language', 'ar');
});
});
});
}
This one is working neat. However, I want to combine a whereBelongsTo(). So this is what I did.
if(isset($request->searchKey) && !empty($request->searchKey) && $request->searchKey !== '')
{
$data['searchKey'] = $request->searchKey;
$searchKey = trim($data['searchKey']);
$membershipCenters = MembershipCenter::where('name', 'LIKE', '%' . $searchKey . '%')->get();
if(isset($membershipCenters) && !empty($membershipCenters) && count($membershipCenters) > 0) {
$query->where(function ($orQuery) use ($searchKey){
$orQuery->where('name', 'LIKE', '%' . $searchKey . '%')
->orWhere('mobile', 'LIKE', '%' . $searchKey . '%')
->orWhere('card_id', 'LIKE', '%' . $searchKey . '%')
->orWhereHas('employeeLanguage', function ($languageRelationQuery) use ($searchKey){
$languageRelationQuery->where(function($languageQueryEn) use($searchKey) {
$languageQueryEn->where('name', 'LIKE', '%' . $searchKey . '%')
->where('language', 'en');
})
->orWhere(function($olanguageQueryAr) use($searchKey) {
$olanguageQueryAr->where('name', 'LIKE', '%' . $searchKey . '%')
->where('language', 'ar');
});
});
})
->orWhere(function($belongsToQuery) use($membershipCenters) {
$belongsToQuery->whereBelongsTo($membershipCenters, 'employeeMembershipCenter');
});
} else {
$query->where(function ($orQuery) use ($searchKey){
$orQuery->where('name', 'LIKE', '%' . $searchKey . '%')
->orWhere('mobile', 'LIKE', '%' . $searchKey . '%')
->orWhere('card_id', 'LIKE', '%' . $searchKey . '%')
->orWhereHas('employeeLanguage', function ($languageRelationQuery) use ($searchKey){
$languageRelationQuery->where(function($languageQueryEn) use($searchKey) {
$languageQueryEn->where('name', 'LIKE', '%' . $searchKey . '%')
->where('language', 'en');
})
->orWhere(function($olanguageQueryAr) use($searchKey) {
$olanguageQueryAr->where('name', 'LIKE', '%' . $searchKey . '%')
->where('language', 'ar');
});
});
});
}
}
But the result isn't working. When I am combining whereBelongsTo with OR condition using the orWhere(), the data set returns blank. But if simply write
$query->whereBelongsTo($membershipCenters, 'employeeMembershipCenter');
It works as it intends to.
What am I doing wrong?