Hierarchy in Laravel

Hierarchy structure

I am trying to implement subscriber hierarchy structure in my Laravel application.

My Database structure is: subscriber table with primary key id and parent column parent_id and Eloquent relationship in Subscriber Model:

// Each subscriber may have one parent
public function parent()
{
	return $this->belongsTo(Subscriber::class, 'parent_id', 'id')->with('parent');
}

//each subscriber may have many children
public function children() {
	return $this->hasMany(Subscriber::class, 'parent_id', 'id')->with('children');
}

To get multilevel hierarchy data, I have used recursive function and above relationship method to get parent and children data like.

public function getChildren($subscriber=null) {
	$subscriber = $subscriber?:$this->subscriber;
	$children = collect([]);
	return $this->getChildrenRecursive($children, $subscriber);
}

private function getChildrenRecursive($children, $subscriber) {
	foreach ($subscriber->children as $child) {
		$children->push($child);
		$this->getChildrenRecursive($children, $child);
	}
	return $children;
}

Initially, I did not notice the case of cyclic loop/self-linking in multi-level hierarchy. If accidently, cyclic loop exist in data.

$subscriber->children();
$subscriber->parent();

The above method goes infinite loop and server crashes after with Allowed memory size exhausted. And Laravel eloquent does not handle cyclic loop or self-linking by default. What is the best way to handle these scenario in Laravel?

And another issue of using recursive function to get all the child or parent node in multi-level (N-level) hierarchy is performance. How to increase performance? I would appreciate your help. Thanks in advance.

krisi_gjika

3 years ago

Level 14

@govindalohani I would suggest a modification to your data structure, even in the best case scenario you are still doing "n + 1" queries by getting each children in a recursive call.

I would suggest creating another parallel table with: parent_id, child_id, depth. Store all children of a parent and increase the depth for each recursive call. This way when you call ->children() you get children of all levels.

Want first order children only, do: ->children()->where('depth', 0).

Want all parents of an item: ->parents().

Configure parents relation such as:

public function parents(): BelongsToMany
    {
        return $this->belongsToMany(
            __CLASS__,
            'pivot_table_name',
            'child_id',
            'parent_id'
        )
            ->withTimestamps()
            ->withPivot('depth') // order parents by depth (asc), first parent is also direct parent
            ->orderBy('pivot_table_name.depth');
    }

1 like

govindalohani

3 years ago

Level 1

Is there a particular solution to my problem? Because this application is already in production server. And I need to rebuild whole thing if I go with creating another parallel table.

krisi_gjika

2 years ago

Level 14

@govindalohani the solution is to improve your data structure with a pivot table to allow for more efficient queries. What solution did you expect? If my previous response answers your question, please mark it as so.

Please or to participate in this conversation.