CODEWITHSUNDEEP
phpmysqlrecursionlaravel-4
tiffanyhwang
tiffanyhwang

Reputation: 1453

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

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?

Upvotes: 9

Views: 13612

Answers (5)

Ivan Carosati
Ivan Carosati

Reputation: 345

I modified ruuter answer to use relationships. If you have a parent() belongsTo relationship on the model you can use that one to remove the where clause, see below:

public function parents()
{
        $parents = $this->parent()->get();

        while ($parents->last() && $parents->last()->parent_id !== null) {
                $parent = $parents->last()->parent()->get();
                $parents = $parents->merge($parent);
        }

        return $parents;
}

And then you can access it:

public function allParents(): Collection 
{
        return $this->parents();
}

Upvotes: 0

Jonas Staudenmeir
Jonas Staudenmeir

Reputation: 25906

I've created a package that uses common table expressions (CTE) to implement recursive relationships: https://github.com/staudenmeir/laravel-adjacency-list

You can use the ancestors relationship to get all parents of a model recursively:

class YourModel extends Model
{
    use \Staudenmeir\LaravelAdjacencyList\Eloquent\HasRecursiveRelationships;
}

$allParents = YourModel::find($id)->ancestors;

Upvotes: 2

ruuter
ruuter

Reputation: 2533

I modified tiffanyhwang solution and turned it into a non-static method and included a attribute accessor to make it easier to get results.

public function ancestors()
{
    $ancestors = $this->where('id', '=', $this->parent_id)->get();

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

    return $ancestors;
}

and accessor to retrieve a collection of ancestors from model attribute

public function getAncestorsAttribute()
{
    return $this->ancestors();
    // or like this, if you want it the other way around
    // return $this->ancestors()->reverse();
}

so now you can get ancestors like this:

$ancestors = $model->ancestors;

and since its a Collection, you can now easily do for example this:

echo $model->ancestors->implode('title',', ');

Upvotes: 7

tiffanyhwang
tiffanyhwang

Reputation: 1453

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!

Upvotes: 11

WilliamD.
WilliamD.

Reputation: 119

An other way could be to use the etrepat/baum package, it's a Laravel implementation of the Nested set model. It's using an ordered tree that is faster and use non-recursive queries. While your data structured like this : 

root
  |_ Child 1
    |_ Child 1.1
    |_ Child 1.2
  |_ Child 2
    |_ Child 2.1
    |_ Child 2.2

There are structured like this in nested set model :

 ___________________________________________________________________
|  Root                                                             |
|    ____________________________    ____________________________   |
|   |  Child 1                  |   |  Child 2                  |   |
|   |   __________   _________  |   |   __________   _________  |   |
|   |  |  C 1.1  |  |  C 1.2 |  |   |  |  C 2.1  |  |  C 2.2 |  |   |
1   2  3_________4  5________6  7   8  9_________10 11_______12 13  14
|   |___________________________|   |___________________________|   |
|___________________________________________________________________|

And inserting nodes is easy as :

$child1 = $root->children()->create(['name' => 'Child 1']);

Upvotes: 4

Related Questions