Laravel query builder for recursive results? E.g. id, parent_id

Question

So I have data structured like this:

id|parent_id|name
1 |null     |foo
2 |1        |bar
3 |2        |baz

So basically foo->bar->baz. I'm stumped on how to use laravel's query builder to get rows for a child row, then its ancestors (until parent_id == null). Can this be done with laravel? I've done a little research and Postgres has RECURSIVE while MySQL doesn't (Postgres recursive query to update values of a field while traversing parent_id).

I believe MySQL has something similar: How to do the Recursive SELECT query in MySQL?

But how would I implement this in Laravel?

My starting code is basically using a query scope, but I'm just not getting it right:

Model::select('name')->getParent(3); //get baz and the ancestors of baz
protected function scopeGetParent($id) {
  $parent = Model::where('id', '=', $id);
  return $query->getParent($parent->parent_id);
}

The desired result I want is:

name
baz
bar
foo

Any ideas?

Answer 1

So after fiddling around with the merge() method for the Collections class:

public static function ancestors($id)
{
    $ancestors = Model::where('id', '=', $id)->get();

    while ($ancestors->last()->parent_id !== null)
    {
      $parent = Model::where('id', '=', $ancestors->last()->parent_id)->get();
      $ancestors = $ancestors->merge($parent);
    }

    return $ancestors;
}

That will produce what I needed, however I believe it can be more cleaner so please feel free to edit it!