Laravel Filter Many to Many Relationship with whereIn array values

Question

I the following scenario: I have a listings and locations tables. A category has many locations and a listing can belong to many locations (many to many relationship). Here is how my models look like:

Table structure for listings

id,
name,
time_stamps

Listing Model (App\Models\Listing)

public function locations() {
        return $this->belongsToMany('App\Models\Location', 'listing_locations', 
          'listing_id', 'location_id');
    }

Table structure for locations

id,
name,
timestamps

Location Model (App\Models\Location)

public function listings() {
        return $this->belongsToMany('App\Models\Listing', 'listing_locations', 
          'location_id', 'listing_id');
    }

Pivot table (listing_locations) structure:

id,
listing_id,
location_id

Everything works fine - I can get listings with their locations and vice versa. The problems sets in when I want to filter the listings - I want to only get listings which belong to particular locations (the locations are in an array coming from a front end). One problem I have is the location names in the database are Google map location names e.g 207-192 Spadina Ave, Toronto, ON M5T 2C2, Canada but user is only filtering with Canada so I have to use like.

Here is what I have so far:

$listings = Listing::where('id', '>', 0);

    $countries = $request->query('countries');
    
                foreach ($countries as $country) {
                    $conditions[] = ['name', 'like', '%' . $country . '%'];
                }
    
                
                $listings->whereHas('location', function($q) use ($conditions) {
                    $q->where($conditions);
                });

However, this only works when the countries array (filter) has only one value. I have also tried using orWhere but this does not apply the filter.

Have also tried this

$listings->whereHas('location', function($q) use ($countries) {
                for ($i = 0; $i < sizeof($countries); $i++){
                    $q->orwhere('name', 'like',  '%' . $countries[$i] .'%');
                } 
            });

but does not work.

Any idea how this should work?

@JohnLobo dd($countries) has all selected countries in an array array:2 [▼ 0 => "Spain" 1 => "Taiwan" ] — Benjamin Mwendwa Munyoki
Not exact work ... thus the need of applying LIKE % $country % — Benjamin Mwendwa Munyoki

John Lobo John Lobo · Accepted Answer · 2021-07-09T08:44:01

Group multiple where conditions using where callback

 $listings->when(count($countries),function ($query)use ($countries) {
        
         $query->whereHas('location', function($q) use ($countries) {
            $q->where( function($q) use ($countries) {
                foreach ($countries as $country) {
                  $q->orWhere('name', 'like', '%' . $country . '%');
               }
            });
        });
        
});

Laravel Filter Many to Many Relationship with whereIn array values

1 Answers