display all parents 'parent_id' for a specific node

I have a table objectives with parent_id field

public function parent() {
	return $this->belongsTo(Objective::class, 'parent_id');
}

public function directChildren() {
	return $this->hasMany(Objective::class, 'parent_id');
}

public function children() {
	return $this->directChildren()->with('children');
}

So when using with children it show the data recursive starting from null node parent_id

Objective::with('children')->whereNull('parent_id')->get();

So the structure is this:

[
	{id: 1, parent_id: null, name: "A", children: []},
	{id: 2, parent_id: null, name: "B", children: []}
	{id: 3, parent_id: null, name: "C", children: [
		{id: 4, parent_id: 3, name: "D", children: [
			{id: 5, parent_id: 4, name: "E", children: []}
		]},
		{id: 6, parent_id: 3, name: "F", children: []}
	]}
]

How can I see all the parents up and generate the structure ? Ex: starting from node E and show it like this:

[
	{id: 3, parent_id: null, name: "C", children: [
			{id: 4, parent_id: 3, name: "D", children: [
				 {id: 5, parent_id: 4, name: "E", children: []}
			]},
	]}
]

rodrigo.pedra

4 years ago

Level 56

Add an "ancestor" relation, like this:

public function ancestor() {
	return $this->parent()->with('ancestor');
}

Then

Objective::with('ancestor')->firstWhere('name', 'E');

The results will be from the children to the parent, but then you can handle that after fetching from DB.

calin.ionut

4 years ago

Level 5

@rodrigo.pedra

I have allready tried with similar:

public function allParents() {
	rturn $this->parent()->with('allParents')
}

But it shows like this:

$obj_e = Objective::find(5);
$obj_e->allParents;

[
	{id: 4, parent_id: 3, name: "D", all_parents: [
			{id: 3, parent_id: null, name: "C", all_parents: []}				 
	]}
]

actually in reverse order .... and without the data for current objective E

rodrigo.pedra

4 years ago

Level 56

@calin.ionut to include the current one, eager load the allParents relation and return the model itself, not the relation:

$obj_e = Objective::with('allParents')->find(5);
return $obj_e;

To address the "reverse" order you can use a recursive function to convert it to your expected order.

To query in the expected order with the current data structure is very tricky, and would not be very performant.

If you don't want to maintain a recursive function to reverse the order to the expected one, I advice you to use a different approach to store your hierarchical data.

Search about using closure tables (my preference), or adjacency lists (more commonly found), for hierarchical data in SQL.

rodrigo.pedra

4 years ago

Best Answer

Level 56

Example on how you can reverse the relation chain:

<?php // ./routes/console.php

use Illuminate\Database\Eloquent\Collection;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Support\Facades\Artisan;

class Objective extends Model
{
    protected $guarded = [];
}

Artisan::command('local:test', function () {
    $c = new Objective(['id' => 3, 'name' => 'C', 'parent_id' => null]);
    $d = new Objective(['id' => 4, 'name' => 'D', 'parent_id' => 3]);
    $e = new Objective(['id' => 5, 'name' => 'E', 'parent_id' => 4]);

    $c->setRelation('ancestor', null);
    $d->setRelation('ancestor', $c);
    $e->setRelation('ancestor', $d);

    // inverted: start from child to root
    \dump($e->toArray());

    $treeWalker = function (Objective $node) use (&$treeWalker) {
        $parent = $node->ancestor;
        $node->unsetRelation('ancestor');

        if (is_null($parent)) {
            return $node;
        }

        $parent->setRelation('children', Collection::make([$node]));

        return $treeWalker($parent);
    };

    $root = $treeWalker($e);

    // reversed: start from root to child
    \dump($root->toArray());
});

Output:

$ php artisan local:test
^ array:4 [
  "id" => 5
  "name" => "E"
  "parent_id" => 4
  "ancestor" => array:4 [
    "id" => 4
    "name" => "D"
    "parent_id" => 3
    "ancestor" => array:4 [
      "id" => 3
      "name" => "C"
      "parent_id" => null
      "ancestor" => null
    ]
  ]
]
^ array:4 [
  "id" => 3
  "name" => "C"
  "parent_id" => null
  "children" => array:1 [
    0 => array:4 [
      "id" => 4
      "name" => "D"
      "parent_id" => 3
      "children" => array:1 [
        0 => array:3 [
          "id" => 5
          "name" => "E"
          "parent_id" => 4
        ]
      ]
    ]
  ]
]

2 likes

Please or to participate in this conversation.