Recursive self join in Laravel

Question

I have the following table:

| node_id | node_name | parent_id |  
| 1       | Node 1    |           |  
| 2       | Node 2    | 1         |  
| 3       | Node 3    | 1         |  
| 4       | Node 4    | 2         |

I want to retrieve recursively all the nodes under a specific node.

My model looks like this:

class Tree extends Model
{
    public function parent()
    {
        return $this->belongsTo(self::class, 'node_id');
    }

    public function children()
    {
        return $this->hasMany(self::class, 'parent_id', 'node_id');
    }

    public function allchildren()
    {
        return $this->hasMany(self::class, 'parent_id', 'node_id')->with('allchildren');;
    }
}

Now if I want to retrieve all descendants of a node, I do the following:

$node = Tree::where('node_id', 1)->first();
$nodes = $node->allchildren;
        

However the above code is returning only the first level and ignoring deeper levels of children.

What am I doing wrong here?

Edit

As a matter of fact, I understand now that I am actually retrieving all the data properly, it's just I'm seeing the top level of the output array.

The question would be how can I flatten the output to show me the data in a single array?

Edit 2

So I managed to create a function to flat the output into a one-level array using the following code (in the controller for example):

public function flattenTree($array)
{
    $result = [];
    foreach ($array as $item) {
        $result[] = ['node_id'=>$item['node_id'], 'node_name'=>$item['node_name']];
        $result = array_merge($result, $this->flattenTree($item['allchildren']));
    }
    return array_filter($result);
}

and then call it this way:

$flatTree = $this->flattenTree($nodes);

Answer 1

You can do eager loading using with(). So it'd look like

Tree::where('node_id', 1)->with('allchildren')->get();

You could go nested eager loading as well.

Tree::where('node_id', 1)->with('children.children')->get();