How to use Laravel whereBelongsTo with OR condition?

36 Views Asked by At

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?

0

There are 0 best solutions below