Laravel: Querying and accessing child objects in nested relationship with where clauses

Question

I am trying to access the child objects of nested relationships that return many results from the parents object.

Let's say I have 4 models : Country - Provinces - Cities - Municipalities

Their relationships are as follows :

Country Model

class Country extends Eloquent
{
   protected $table = 'countries';

   public function provinces()
   {
       return $this->hasMany('Province');
   }
}

Province Model

class Province extends Eloquent
{
   protected $table = 'provinces';

   public function cities()
   {
       return $this->hasMany('City');
   }
   public function country()
   {
       return $this->belongsTo('Country');
   }
}

City Model

class City extends Eloquent
{
   protected $table = 'cities';

   public function municipalities()
   {
       return $this->hasMany('Municipality');
   }
   public function province()
   {
       return $this->belongsTo('Province');
   }
}

Municipality Model

class Municipality extends Eloquent
{
   protected $table = 'municipalities';

   public function cities()
   {
      return $this->belongsTo('City');
   }
 }

Now what I am trying to do is get all municipalities in a given country that have a population over 9000 and are located in provinces that are considered West.

So far I have something like this :

   $country_id = 1;

   $country = Country::whereHas('provinces', function($query){
        $query->where('location', 'West');
        $query->whereHas('cities', function($query){ 
            $query->whereHas('municipalities', function($query){
                $query->where('population', '>', 9000);
            });            
        });
    })->find($country_id); 

Now I can easily get the provinces with $country->provinces but I can't go any deeper than that.

EDIT1 : Fixing the belongsTo relationship as noticed by Jarek.

EDIT2: In addition to Jarek's answer, I wanted to share what I also found however Jarek's is probably the more proper method.

Instead of trying to go from top to bottom (Country -> Municipality) I decided to try the other way (Municipality -> Country) Here's how it works (and I tested it, also works)

          $municipalities = Municipality::where('population', '>', 9000)
            ->whereHas('city', function($q) use ($country_id){
                $q->whereHas('province', function($q) use ($country_id){
                    $q->where('location', 'West');
                    $q->whereHas('country', function($q) use ($country_id){
                          $q->where('id', $country_id);
                    });
                });
            })->get();

I have no idea if this is an actual proper way or if performance would be accepted but it seemed to do the trick for me however Jarek's answer looks more elegant.

Answer 1

Your Municipality-City is probably belongsTo, not hasMany like in the paste.

Anyway you can use hasManyThrough relation to access far related collection:

Country - City
Province - Municipality

Unfortunately there is no relation for 3 level nesting, so you can't do this just like that.

---

Next, your code with whereHas does not limit provinces to west and municipalities to 9000+, but only limits countries to those, that are related to them. In your case this means that result will be either Country (if its relations match these requirements) or null otherwise.

So if you really want to limit related collections, then you need this piece:

$country = Country::with(['provinces' => function($query){
    $query->where('location', 'West');
}, 'provinces.cities.municipalities' => function ($query){
    $query->where('population', '>', 9000);
}])->find($country_id);

This is applying eager loading constraints, and what it does is:

1. loads only West provinces for country with id 1
2. loads all the cities in these provinces
3. loads only 9k+ municipalities in these cities

Since you're not interested in cities, you could use hasManyThrough on the Province:

// Province model
public function municipalities()
{
  return $this->hasManyThrough('Municipality', 'City');
}

then:

$country = Country::with(['provinces' => function($query){
    $query->where('location', 'West');
}, 'provinces.municipalities' => function ($query){
    $query->where('population', '>', 9000);
}])->find($country_id);

---

However in both cases you can't access the municipalities directly, but only like this:

// 1 classic
$country->provinces->first()->cities->first()->municipalities;
// 2 hasManyThrough
$country->provinces->first()->municipalities;

That being said, if you'd like to work with all those municipalities, you need this trick:

$country = Country::with(['provinces' => function($query){
    $query->where('location', 'West');
}, 'provinces.municipalities' => function ($query) use (&$municipalities) {

    // notice $municipalities is passed by reference to the closure
    // and the $query is executed using ->get()
    $municipalities = $query->where('population', '>', 9000)->get();
}])->find($country_id);

This will run additional query, but now all the municipalities are in single, flat collection, so it is very easy to work with. Otherwise you likely end up with a bunch of foreach loops.