Laravel: Recursively compute count of parents of an eloquent relationship (get the relationship depth)

Question

Consider the node chart below:

As shown in the image above, I want to create a function that can tell me the depth of a relationship from the top-level node.

Instance Model

/**
 * Get the immediate parent instance of the instance.
 */
public function parent()
{
    return $this->hasOne(Instance::class);
}

/**
 * Get the children instances of the instance.
 */
public function children()
{
    return $this->hasMany(Instance::class);
}

/**
 * Get the depth of an instance.
 * @return int
 */
public function getDepthAttribute()
{
    // TODO
}

Instance Table

Schema::create('instances', function (Blueprint $table) {
    $table->id();
    $table->string('name', 32);
    $table->foreignId('instance_id')->nullable();
    $table->timestamps();
});

Schema::table('instances', function (Blueprint $table)
{
    $table->foreign('instance_id')->references('id')->on('instances')->onUpdate('cascade')->onDelete('cascade');
});

Instance table example

id    name     instance_id | (getDepthAttribute() should return)
-------------------------- |
1     A        NULL        |  0
2     B        1           |  1
3     C        2           |  2
4     D        3           |  3
5     E        3           |  3
6     F        3           |  3

In a phrase, my problem is: "If an instance has a parent instance, add 1. Repeat until the parent instance does not have a parent instance. Then return the final value."

How can I accomplish this properly in Laravel?

Answer 1

Below is the solution I came up with:

DepthHelper.php

use \App\Models\Instance;
/**
 * Returns the depth of an Instance
 * @param $idToFind
 * @return int
 */
function DepthHelper($idToFind){
    return GetParentHelper($idToFind);
}

// Recursive Helper function
function GetParentHelper($id, $depth = 0) {
    $model = Instance::find($id);

    if ($model->instance_id != null) {
        $depth++;

        return GetParentHelper($model->instance_id, $depth);
    } else {
        return $depth;
    }
}

Instance Model

    /**
     * Get the depth of this instance from the top-level instance.
     */
    public function getDepthAttribute()
    {
        return DepthHelper($this->id);
    }

    protected array $appends = ['depth'];