I have Tasks that have many-to-many relationships with Locations. I want the user to be able to select "home" and "work" as an example out of a list of locations and then a toggle switch for AND vs OR where switched between these two behaviours: 1-AND All tasks if they have any labels that is either HOME or WORK or BOTH will all be loaded. (This is working perfectly in my code) 2-OR(BAD LABEL BUT JUST TRYING TO GET IT TO WORK) All tasks that have both labels attached and only both will be loaded. Not if they only have one.

Below is my code where the 2nd requirement is not working and returns and empty list when there are results to be returned.

return \App\Models\Task::query()
            ->with('locations')
            ->with('tags')
            ->with('ai')
            ->with('entities')
            ->with('delegatee')
            ->with('event')
            ->with('time')
            ->tap(function ($query) {
                if($this->where) {
                    $query->whereHas('locations', function($q)
                    {
                        if($this->where_method=="AND"){ 
                            $q->whereIn(  'locations.slug', $this->where );
                        }else { 
                            $q->where(function($query) {
                                foreach($this->where as $where) {
                                    $query->where('locations.slug', $where );
                                } 
                            });
                        }
                    
                    });
                } 
                if($this->sortBy) {
                    return $query->orderBy($this->sortBy, $this->sortDirection);
                 }else { 
                    return $query;
                 }
            }) 
            ->paginate($this->per_page);

My only idea is that the hasWhere closure only joins one instance of Location as random to check if there is any location or not and so the where filter located in the wrong place in query will never see both and only one joined during that where clause? Its a livewire component and a little tricky to see my computer query but I will dig in to see if thats more helpful and copy it here.

I have Tasks that have many-to-many relationships with Locations. I want the user to be able to select "home" and "work" as an example out of a list of locations and then a toggle switch for AND vs OR where switched between these two behaviours: 1-AND All tasks if they have any labels that is either HOME or WORK or BOTH will all be loaded. (This is working perfectly in my code) 2-OR(BAD LABEL BUT JUST TRYING TO GET IT TO WORK) All tasks that have both labels attached and only both will be loaded. Not if they only have one.

Below is my code where the 2nd requirement is not working and returns and empty list when there are results to be returned.

return \App\Models\Task::query()
            ->with('locations')
            ->with('tags')
            ->with('ai')
            ->with('entities')
            ->with('delegatee')
            ->with('event')
            ->with('time')
            ->tap(function ($query) {
                if($this->where) {
                    $query->whereHas('locations', function($q)
                    {
                        if($this->where_method=="AND"){ 
                            $q->whereIn(  'locations.slug', $this->where );
                        }else { 
                            $q->where(function($query) {
                                foreach($this->where as $where) {
                                    $query->where('locations.slug', $where );
                                } 
                            });
                        }
                    
                    });
                } 
                if($this->sortBy) {
                    return $query->orderBy($this->sortBy, $this->sortDirection);
                 }else { 
                    return $query;
                 }
            }) 
            ->paginate($this->per_page);

My only idea is that the hasWhere closure only joins one instance of Location as random to check if there is any location or not and so the where filter located in the wrong place in query will never see both and only one joined during that where clause? Its a livewire component and a little tricky to see my computer query but I will dig in to see if thats more helpful and copy it here.

• laravel
• eloquent
• many-to-many
• eloquent-relationship

1 Answer 1

It was a lot easier than what I had written:

if($this->where) {
                $q->whereHas('locations', function($q)
                {   
                        $q->whereIn( 'locations.slug', $this->where );  
                }, '>=', $this->where_method=="OR"?1:count($this->where));
            } 

The extra parameters of whereHas is exactly what I needed, you can specify an operator and count value to further customize the query::

whereHas($queryStuff, '>=', $this->where_method=="OR"?1:count($this->where));