php Laravel 递归关系

Laravel Recursive Relationships

提问by Troncoso

I'm working on a project in Laravel. I have an Account model that can have a parent or can have children, so I have my model set up like so:

我正在Laravel 中开展一个项目。我有一个可以有父级或可以有子级的 Account 模型，所以我的模型设置如下：

public function immediateChildAccounts()
{
    return $this->hasMany('Account', 'act_parent', 'act_id');
}

public function parentAccount()
{
    return $this->belongsTo('Account', 'act_parent', 'act_id');
}

This works fine. What I want to do is get all children under a certain account. Currently, I'm doing this:

这工作正常。我想要做的是让所有孩子都在某个帐户下。目前，我正在这样做：

public function allChildAccounts()
{
    $childAccounts = $this->immediateChildAccounts;
    if (empty($childAccounts))
        return $childAccounts;

    foreach ($childAccounts as $child)
    {
        $child->load('immediateChildAccounts');
        $childAccounts = $childAccounts->merge($child->allChildAccounts());
    }

    return $childAccounts;
}

This also works, but I have to worry if it's slow. This project is the re-write of an old project we use at work. We will have several thousand accounts that we migrate over to this new project. For the few test accounts I have, this method poses no performance issues.

这也有效，但我不得不担心它是否很慢。这个项目是我们在工作中使用的一个旧项目的重写。我们将有数千个帐户迁移到这个新项目。对于我拥有的少数测试帐户，此方法不会造成性能问题。

Is there a better solution? Should I just run a raw query? Does Laravelhave something to handle this?

有更好的解决方案吗？我应该只运行原始查询吗？确实Laravel有东西来处理呢？

In summaryWhat I want to do, for any given account, is get every single child account and every child of it's children and so on in a single list/collection. A diagram:

总之，对于任何给定的帐户，我想要做的是将每个子帐户及其子帐户的每个子项都放在一个列表/集合中。一张图：

A -> B -> D
|--> C -> E
     |--> F 
G -> H

If I run A->immediateChildAccounts(), I should get {B, C}
If I run A->allChildAccounts(), I should get {B, D, C, E, F} (order doesn't matter)

如果我运行 A->immediateChildAccounts()，我应该得到 {B, C}
如果我运行 A->allChildAccounts()，我应该得到 {B, D, C, E, F} （顺序无关紧要）

Again, my method works, but it seems like I'm doing way too many queries.

同样，我的方法有效，但似乎我做的查询太多了。

Also, I'm not sure if it's okay to ask this here, but it is related. How can I get a list of all accounts that don'tinclude the child accounts? So basically the inverse of that method above. This is so a user doesn't try to give an account a parent that's already it's child. Using the diagram from above, I want (in pseudocode):

另外，我不确定在这里问这个问题是否可以，但它是相关的。如何获取不包括子帐户的所有帐户的列表？所以基本上与上述方法相反。这样用户就不会尝试将已经是其子项的父级帐户分配给该帐户。使用上面的图表，我想要（在伪代码中）：

Account::where(account_id not in (A->allChildAccounts())). So I would get {G, H}

Account::where(account_id 不在 (A->allChildAccounts())) 中。所以我会得到 {G, H}

Thanks for any insight.

感谢您的任何见解。

回答by Jarek Tkaczyk

This is how you can use recursive relations:

这是您如何使用递归关系：

public function childrenAccounts()
{
    return $this->hasMany('Account', 'act_parent', 'act_id');
}

public function allChildrenAccounts()
{
    return $this->childrenAccounts()->with('allChildrenAccounts');
}

Then:

然后：

$account = Account::with('allChildrenAccounts')->first();

$account->allChildrenAccounts; // collection of recursively loaded children
// each of them having the same collection of children:
$account->allChildrenAccounts->first()->allChildrenAccounts; // .. and so on

This way you save a lot of queries. This will execute 1 query per each nesting level + 1 additional query.

这样您就可以节省大量查询。这将为每个嵌套级别执行 1 个查询 + 1 个附加查询。

I can't guarantee it will be efficient for your data, you need to test it definitely.

我不能保证它对您的数据有效，您需要对其进行绝对测试。

---

---

This is for childless accounts:

这适用于无子女账户：

public function scopeChildless($q)
{
   $q->has('childrenAccounts', '=', 0);
}

then:

然后：

$childlessAccounts = Account::childless()->get();

回答by George

public function childrenAccounts()
{
    return $this->hasMany('Account', 'act_parent', 'act_id')->with('childrenAccounts');
}

This code returns all children accounts (recurring)

此代码返回所有子帐户（重复）

回答by Meki

We're doing something similar, but our solution was this:

我们正在做类似的事情，但我们的解决方案是这样的：

class Item extends Model {
  protected $with = ['children'];

  public function children() {
    $this->hasMany(App\Items::class, 'parent_id', 'id');
 }
}

回答by Kurucu

I'm doing something similar. I think the answer is to cache the output, and clear the cache any time the database is updated (provided your accounts themselves don't change much?)

我正在做类似的事情。我认为答案是缓存输出，并在数据库更新时清除缓存（前提是您的帐户本身没有太大变化？）

回答by Vahid Amiri

For future reference:

备查：

public function parent()
{
    // recursively return all parents
    // the with() function call makes it recursive.
    // if you remove with() it only returns the direct parent
    return $this->belongsTo('App\Models\Category', 'parent_id')->with('parent');
}

public function child()
{
    // recursively return all children
    return $this->hasOne('App\Models\Category', 'parent_id')->with('child');
}

This is for a Categorymodel that has id, title, parent_id. Here's the database migration code:

这是针对Category具有id, title, parent_id. 这是数据库迁移代码：

    Schema::create('categories', function (Blueprint $table) {
        $table->increments('id');
        $table->timestamps();
        $table->string('title');
        $table->integer('parent_id')->unsigned()->nullable();
        $table->foreign('parent_id')->references('id')->on('categories')->onUpdate('cascade')->onDelete('cascade');
    });

回答by Jonas Staudenmeir

I've created a package that uses common table expressions (CTE) to implement recursive relationships: https://github.com/staudenmeir/laravel-adjacency-list

我创建了一个使用公用表表达式 (CTE) 来实现递归关系的包：https: //github.com/staudenmeir/laravel-adjacency-list

You can use the descendantsrelationship to get all children of an account recursively:

您可以使用该descendants关系以递归方式获取帐户的所有子项：

class Account extends Model
{
    use \Staudenmeir\LaravelAdjacencyList\Eloquent\HasRecursiveRelationships;
}

$allChildren = Account::find($id)->descendants;

回答by Alex Fichter

I think I've come up with a decent solution as well.

我想我也想出了一个不错的解决方案。

class Organization extends Model
{
    public function customers()
    {
        return $this->hasMany(Customer::class, 'orgUid', 'orgUid');
    }

    public function childOrganizations()
    {
        return $this->hasMany(Organization::class, 'parentOrgUid', 'orgUid');
    }

    static function addIdToQuery($query, $org)
    {
        $query = $query->orWhere('id', $org->id);
        foreach ($org->childOrganizations as $org)
        {
            $query = Organization::addIdToQuery($query, $org);
        }
        return $query;
    }

    public function recursiveChildOrganizations()
    {
        $query = $this->childOrganizations();
        $query = Organization::addIdToQuery($query, $this);
        return $query;
    }

    public function recursiveCustomers()
    {
         $query = $this->customers();
         $childOrgUids = $this->recursiveChildOrganizations()->pluck('orgUid');
         return $query->orWhereIn('orgUid', $childOrgUids);
    }

}

Basically, I'm starting with a query builder relationship and adding orWhere conditions to it. In the case of finding all of the child organizations, I use a recursive function to drill down through the relationships.

基本上，我从查询构建器关系开始，并向其添加 orWhere 条件。在找到所有子组织的情况下，我使用递归函数来深入了解这些关系。

Once I have the recursiveChildOrganizations relationship, I've run the only recursive function needed. All of the other relationships (I've shown recursiveCustomers but you could have many) use this.

一旦我有了 recursiveChildOrganizations 关系，我就运行了唯一需要的递归函数。所有其他关系（我已经展示了 recursiveCustomers 但你可以有很多）使用这个。

I avoid instantiating the objects at every possible turn, since the query builder is so much faster than creating models and working with collections.

我避免在每一个可能的回合实例化对象，因为查询构建器比创建模型和使用集合要快得多。

This is much faster than building a collection and recursively pushing members to it (which was my first solution), and since each method returns a query builder and not a collection, it stacks wonderfully with scopes or any other conditions you want to use.

这比构建一个集合并递归地将成员推送到它（这是我的第一个解决方案）要快得多，并且由于每个方法返回一个查询构建器而不是一个集合，它与范围或您想要使用的任何其他条件完美地堆叠在一起。