Laravel Query with Relation and Where/orWhere for Search field

Question

there are 2 models. Customers & Locations.

In a location single page, it should now be possible to display all customers from the location and filter them according to customer attributes.

The problem here is that when you enter something in the search field, customers are no longer picked out specifically from the location, but from all customers that exist.

Filtering is possible on the overview page (where all customers are displayed). However, as soon as the query is added that it should be specifically for customers who own the location, it is no longer possible.

return \App\Models\Customer::with('locations')

                ->whereHas('locations', function($q)

                {$q->where('locations.id', $this->location->id)

                    ->Where('customers.name','like', '%'.$this->search.'%')

                    ->orWhere('customers.domain','like', '%'.$this->search.'%')

                    ->orWhere('customers.number','like', '%'.$this->search.'%')

                    ->orWhere('customers.email','like', '%'.$this->search.'%'); })

                    ->orderBy($this->sortField, $this->sortDirection)

                    ->paginate($this->page_number);

Unfortunately, I am not sure how to build the query that requires the first whereHas (which is only to find customers from the locations) and then the other Where commands for the search field.

Check this post on how to group where clauses: https://stackoverflow.com/a/22322422/536590 — Daantje, Mar 22 '22 at 15:45

score 1 · Answer 1 · answered Mar 22 '22 at 15:47

To filter by specific location and using the search value

return \App\Models\Customer::with('locations')
    ->whereHas('locations', function($q) {
        $q->where('id', $this->location->id);
    })
    ->where(function($q) {
        $q->Where('name','like', '%'.$this->search.'%')
            ->orWhere('domain','like', '%'.$this->search.'%')
            ->orWhere('number','like', '%'.$this->search.'%')
            ->orWhere('email','like', '%'.$this->search.'%');
    })
    ->orderBy($this->sortField, $this->sortDirection)
    ->paginate($this->page_number);

Laravel Query with Relation and Where/orWhere for Search field

1 Answers1