CODEWITHSUNDEEP
phplaraveleloquent
Nitish Kumar
Nitish Kumar

Reputation: 6296

Search where clause orWhere clause in Laravel

I'm having two models Company and Contact search controller, my Contact model look something like:

class Contact extends Model {

    public function companies()
    {
        return $this->belongsToMany('App\Company', 'company_contact', 'contact_id', 'company_id');
    }

}

Few of my companies are referred as client for which I have a boolean field under the name of is_client.

I want to search contacts through a param request search which compares contact's first_name and last_name also with company name. If any body wants to have contact list of all the client then they can pass a extra request of client so accordingly I made a controller something like this:

Contact::when($request->client && $request->search, function ($q) use($request) {
    $q->whereHas('companies', function ($q) use ($request) {
        $q->where('is_client', true)->where(function ($q) use ($request) {
            $q->orWhere('name', 'like', '%'. $request->search . '%');
        });
    })->where(function ($q) use ($request) {
        $q->orWhere('first_name', 'like', '%'.$request->search.'%')
            ->orWhere('last_name', 'like', '%'.$request->search.'%');
    });
})->when($request->client == null && $request->search, function ($q) use($request) {
    $q->where('first_name', 'like', '%'.$request->search.'%')
        ->orWhere('last_name', 'like', '%'.$request->search.'%')
        ->orWhereHas('companies', function ($q) use ($request) {
            $q->where('name', 'like', '%' . $request->search . '%');
        });
})->when($request->client && $request->search == null, function ($q) use ($request) {
    $q->whereHas('companies', function ($q) use ($request) {
        $q->where('is_client', true);
    });
})
    ->paginate(30)

It work perfectly fine with non-client contacts and for client contact initial list appears fine but when we type something it behaves not as desired. I may be doing some logic error. Help me out in this or guide me for better approach.

Upvotes: 0

Views: 890

Answers (1)

N69S
N69S

Reputation: 17216

Your conditions when you got client and search are not correctly grouped

Contact::when($request->client && $request->search, function ($q) use($request) {
    $q->whereHas('companies', function ($q) use ($request) {
        $q->where('is_client', true);
    })->where(function ($q) use ($request) {
        $q->where('first_name', 'like', '%'.$request->search.'%')
        ->orWhere('last_name', 'like', '%'.$request->search.'%')
        ->orWhereHas('companies', function ($q) use ($request) {
            $q->where('name', 'like', '%' . $request->search . '%');
        });
    });
})->when($request->client == null && $request->search, function ($q) use($request) {
    $q->where('first_name', 'like', '%'.$request->search.'%')
        ->orWhere('last_name', 'like', '%'.$request->search.'%')
        ->orWhereHas('companies', function ($q) use ($request) {
            $q->where('name', 'like', '%' . $request->search . '%');
        });
})->when($request->client && $request->search == null, function ($q) use ($request) {
    $q->whereHas('companies', function ($q) use ($request) {
        $q->where('is_client', true);
    });
})
    ->paginate(30)

the condition is_client must be separated from the condition of search

You should have noticed that you can easily shrink the code here

$contactQuery = Contact::query();
if ($request->client) {
    $contactQuery->whereHas('companies', function ($q) {
        $q->where('is_client', true);
    });
}

if ($request->search) {
    $contactQuery->where(function ($q) use ($request) {
        $q->where('first_name', 'like', '%'.$request->search.'%')
        ->orWhere('last_name', 'like', '%'.$request->search.'%')
        ->orWhereHas('companies', function ($q) use ($request) {
            $q->where('name', 'like', '%' . $request->search . '%');
        });
    });
}

$contacts = $contactQuery->paginate(30);

Upvotes: 1

Related Questions