Laravel: querying and accessing child objects in a nested relationship with offers

Question

I am trying to access children of nested relationships that return a lot of results from a parent object.

Say I have 4 models: Country - Provinces - Cities - Municipalities

Their relationship is 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'm trying to do is get all the municipalities in this country with a population of more than 9,000 people and located in the provinces that are considered to be the 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 provinces with $country->provinces , but I cannot go deeper.

EDIT1: Fixing the applyTo relationship, as Yarek noted.

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

Instead of trying to go from top to bottom (Country → Municipality), I decided to try a different path (Municipality → Country) Here, 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 really the right way or if the performance will be accepted, but it seems to be a trick for me, but Jarek's answer looks more elegant.

Answer 1

Your Municipality - City probably belongsTo , and not hasMany , as in the paste.

In any case, you can use the hasManyThrough relation to access a significantly related collection:

 Country - City Province - Municipality 

Unfortunately, there is no relationship for level 3, so you cannot do it that way.

---

Then your code with whereHas does not limit provinces to west and municipalities to 9000+ , but limits countries those associated with them. In your case, this means that the result will be either Country (if its relationship meets these requirements), or null otherwise.

So, if you really want to limit related collections, you will need this part:

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

This application requires intensive download restrictions, and what it does:

 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 are not interested in cities, you can use hasManyThrough in 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 cannot directly contact the municipalities, but only this way:

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

If you want to work with all these 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 cause an additional request, but now all the municipalities are in a single, flat collection, so it is very easy to work with it. Otherwise, you are likely to get a bunch of foreach loops.