Laravel雄辩的递归儿童

Question

The structure that I've got is as follows

User table

id       PK
name
email
username
password

UserHierarchy table

user_parent_id FK of User
user_child_id  FK of User 
(Composite primary key)

I've written these 2 relationships, in order to retrieve who is the father of a user and who is a child of a user

public function parent()
{
    return $this->hasManyThrough(\App\Models\User::class, \App\Models\UserHierarchy::class, 'user_child_id', 'id', 'id', 'user_parent_id');
}

public function children()
{
    return $this->hasManyThrough(\App\Models\User::class, \App\Models\UserHierarchy::class, 'user_parent_id', 'id', 'id', 'user_child_id');
}

In order to get all the children, grandchildren and so on, I've developed this adicional relationship, that takes use of eager loading

public function childrenRecursive()
{
        return $this->children()->with('childrenRecursive.children');
}

So far so good, when I find a User with an id, I can get all the downwards tree by using childrenRecursive. What I'm trying to achieve now is to re-use these relationships to filter a certain set of results, and by that I mean: When it's a certain User (for example id 1), I want a collection of Users that belong in his downward tree (children recursive) and his first direct parents as well.

$model->where(function ($advancedWhere) use ($id) {
    $advancedWhere->whereHas('parent', function ($advancedWhereHas) use ($filterValue) {
        $advancedWhereHas->orWhere('user_child_id', $id);
        //I want all users that are recorded as his parents
 })->whereHas('childrenRecursive', function ($advancedWhereHas) use ($id) {
        // Missing code, I want all users that are recorded as his children and downwards
 })->get();

This is the complete tree I'm testing and the result produced above (if I add a similar orWhere on the childrenRecursive) is that it returns every User that has a Parent-Child relationship. E.g User 2 should return every number except 11 and 12, and it's returning every number except 11 (because 11 is not a child of anyone)

Answer 1

I'm going to answer your question first, but in the second half of the answer I have proposed an alternative which I strongly suggest adopting.

MySQL (unlike, incidentally, Microsoft SQL) doesn't have an option to write recursive queries. Accordingly, there is no good Laravel relationship to model this.

As such, there is no way for Laravel to do it other than naively, which, if you have a complex tree, is going to lead to many queries.

Essentially when you load your parent, you will only have access to its children (as a relationship collection). Then you would foreach through its children (and then their children, etc, recursively) to generate the whole tree. Each time you do this, it performs new queries for the child and its children. This is essentially what you're currently doing, and you will find that as your data set grows it is going to start becoming very slow. In the end, this provides you with a data structure on which you can apply your filters and conditions in code. You will not be able to achieve this in a single query.

If you are writing to the db a lot, i.e. adding lots of new children but rarely reading the results, then this may be your best solution.

(Edit: abr's comment below linked me to the release notes for MySQL 8 which does have this functionality. My initial response was based on MySQL 5.7. However, I'm not aware of Laravel/Eloquent having a canonical relationship solution employing this yet. Furthermore I have previously used this functionality in MSSQL and nested sets are a better solution IMO.

Furthermore, Laravel isn't necessarily coupled to MySQL - it just often is the db of choice. It will therefore probably never use such a specific solution to avoid such tight coupling.)

---

However most hierarchical structure read more than they write, in which case this is going to start stressing your server out considerably.

If this is the case, I would advise looking into:

https://en.wikipedia.org/wiki/Nested_set_model

We use https://github.com/lazychaser/laravel-nestedset which is an implementation of the above, and it works very well for us.

It is worth mentioning that it can be slow and memory intensive when we redefine the whole tree (we have around 20,000 parent-child relationships), but this only has to happen when we've made an error in the hierarchy that can't be unpicked manually and this is rare (we haven't done it in 6 months). Again, if you think you may have to do that regularly, this may not be the best option for you.