Laravel Recursive Relationships

Question

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:

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 Laravel have something to handle this?

In summary What 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)

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't include 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}

Thanks for any insight.

Answer 1

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.

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();

Answer 2

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

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

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

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

Answer 3

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

This code returns all children accounts (recurring)

Answer 4

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 Category model that has id, title, parent_id. Here's the database migration code:

    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');
    });

Answer 5

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');
 }
}

Answer 6

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.

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.

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.

Answer 7

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?)

Answer 8

You could also add this relation to the "with" property of your model if you think this is something that you need always to have access to :

protected $with = [
    'childrenAccounts'
];

Answer 9

I made managed_by in Table (users) and this Solution get all unlimited levels of children Recursively.

in [User] Model

 public function Childs(){
        return $this->hasMany('App\User', 'managed_by', 'id')->with('Childs');
    }

in [helpers] file (My magic Solution )

if (!function_exists('user_all_childs_ids')) {
        function user_all_childs_ids(\App\User $user)
        {
            $all_ids = [];
            if ($user->Childs->count() > 0) {
                foreach ($user->Childs as $child) {
                    $all_ids[] = $child->id;
                    $all_ids=array_merge($all_ids,is_array(user_all_childs_ids($child))?user_all_childs_ids($child):[] );
                }
            }
            return $all_ids;
        }
    }