Get position of a node in its hierarchy level?

[ludgerey]

Is there a way to determine a nodes position in the ordered tree?

My goal is to have index numbering like in this tree without going through the tree all the time (e.g. when rendering a single node)?

[ 1 ]     House 1
[ 1.1 ]   - Tenant Miller
[ 1.2 ]   - Tenant Lee
[ 2 ]     House 2
[ 2.1 ]   - Tenant Lisa
[ 2.2 ]   - Tenant Maria
[ 2.2.1 ] -- Room A
[ 2.2.2 ] -- Room B

[leandrodiogenes]

Hi @ludgerey! I dont know if is the best way but that is how i'm using. This is my accessors on model.

public function getBreadcrumbAttribute(){
    return $this->ancestors->count() ? implode(' > ', $this->ancestors->pluck('name')->toArray()) . " > " . $this->name : $this->name;
}

public function getBreadcrumbCodeAttribute(): ?string {
    return $this->ancestors->count() ? implode('.', $this->ancestors->pluck('code')->toArray()) . "." . $this->code : $this->code;
}

[leandrodiogenes]

i'm using this package to generate my 'code' column. https://github.com/spatie/eloquent-sortable

public $sortable = [
    'order_column_name'  => 'code',
    'sort_when_creating' => false,
];

public function buildSortQuery() {
    return static::query()->defaultOrder()->where('parent_id', $this->parent_id);
}

[ludgerey]

To determine the position of elements in the same level I have found a solution, based on this article.

The proposed raw sql is:

SELECT COUNT(*) as `position` FROM `nodes` WHERE `parent_node_id` = ? AND `left` < ?

I used this to build a subquery which I just post here:

$query = Unit::scoped(['project_id' => $this->project->id])
    ->defaultOrder()
    ->withDepth();

$sub = (new Unit())
    ->setRawAttributes(['project_id' => $this->project->id])
    ->newScopedQuery('_p')
    ->toBase()
    ->selectRaw('COUNT(*) AS `position`')
    ->fromRaw('`units` as `_p`')
    ->whereRaw("`_p`.`parent_id` <=> `units`.`parent_id` AND `_p`.`_lft` < `units`.`_lft`")
    ->whereNull('deleted_at');

$query->selectSub($sub, 'position');

$results = $query->get();

With some extra work, this could be converted to a a similar method like withDepth.