Laravel：查询和访问与 where 子句嵌套关系的子对象

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

提问by sholmes

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

假设我有 4 个模型：国家 - 省 - 城市 - 直辖市

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.

现在我想要做的是获取给定国家/地区中人口超过 9000 且位于被认为是西部省份的所有城市。

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->provincesbut I can't go any deeper than that.

现在我可以轻松地获得省份，$country->provinces但我不能再深入了。

EDIT1 : Fixing the belongsTo relationship as noticed by Jarek.

EDIT1：修复 Jarek 注意到的belongsTo 关系。

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

EDIT2：除了 Jarek 的回答之外，我还想分享我发现的内容，但 Jarek 的可能是更合适的方法。

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.

我不知道这是否是一种实际的正确方式，或者性能是否会被接受，但它似乎对我有用，但是 Jarek 的回答看起来更优雅。

回答by Jarek Tkaczyk

Your Municipality-Cityis probably belongsTo, not hasManylike in the paste.

您的Municipality-City可能是belongsTo，hasMany不像粘贴中那样。

Anyway you can use hasManyThroughrelation to access far related collection:

无论如何，您可以使用hasManyThrough关系来访问相关的集合：

Country - City
Province - Municipality

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

不幸的是，3 级嵌套没有关系，所以你不能那样做。

---

---

Next, your code with whereHasdoes not limit provincesto westand municipalitiesto 9000+, but only limits countriesto those, that are related to them. In your case this means that result will be either Country(if its relations match these requirements) or nullotherwise.

接下来，您的代码whereHas不限制provinces到west和municipalities到9000+，但只限制countries于那些，那些与他们有关。在您的情况下，这意味着结果将是Country（如果其关系符合这些要求）或null其他。

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 hasManyThroughon the Province:

由于您对城市不感兴趣，您可以hasManyThrough在省上使用：

// 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.

这将运行额外的查询，但现在所有的自治市都在单一的、扁平的集合中，因此很容易使用。否则你可能会得到一堆 foreach 循环。