Limiting recursive relationship depth when eager loading

I'm probably approaching this problem in the wrong way, but I have a simple recursive relationship in my 'Post' model ...

    public function commentsRecursive(): HasMany
    {
        return $this->comments()->with('commentsRecursive');
    }

... which I'm eager loading before I pass to my view:

            'posts' => Post::orderBy('updated_at', 'desc')
                ->with(['commentsRecursive'])
                ->get()

I'd like to be able to specify a recursion depth, but is there a way to pass a parameter with the eager loading?

Something like:

            'posts' => Post::orderBy('updated_at', 'desc')
                ->with(['commentsRecursive(2)'])
                ->get()

tisuchi

2 years ago

Level 70

@bwrigley Based on my understanding, you cannot do that.

But you probably use nested eager loading to limit it down.

For example, the following code returns 3 levels deep relationship:

'posts' => Post::orderBy('updated_at', 'desc')
    ->with(['commentsRecursive.commentsRecursive.commentsRecursive'])   // This goes 3 levels deep
    ->get()

1 like

bwrigley

2 years ago

Level 5

@tisuchi thanks for your advice. Yes I've used that nested approach before and I'll probably end up doing similar again.

krisi_gjika

2 years ago

Level 14

I think you should reconsider your design, if you need to query more than one level deep your performance will suffer

bwrigley

2 years ago

Level 5

@krisi_gjika thanks for your reply. Can I ask how you would do it?

So I have a very standard forum. Users can Post and Posts can have Comments and Comments can have Comments etc etc.

Can you think of a non-recursive way to retrieve a Post with its Comments and it's Comments' Comments etc etc.?

krisi_gjika

2 years ago

Best Answer

Level 14

@bwrigley I worked on a similar project where a user can have a company and that company can have other companies and so on in a tree structure, and I had trouble querying all the child companies since I didn't know how deep I would have to go.

What I ended up doing, was to create a new "helper" table to break the recursive nature of "$company->parent->parent" into "$company->parents". So in your case it would be

comments
id | post_id | parent_comment_id | content
4  | 10      | null
5  | 10      | 4
6  | 10      | 5

comment_children
post_id (optional) | comment_id | child_comment_id | through_id | depth
10                 | 4          | 5                | null       | 1
10                 | 4          | 6                | 5          | 2
10                 | 5          | 6                | null       | 1

now on your Comment model you can create children, parent and parents relations, order children by depth, query depth = 1 to get direct children and so on.

Now if you query $post->load('comments.children') you will get all children comments of top level comments no matter what depth they might be on. You can order them by depth/child_comment_id or limit the number loaded, ect. depending on your need.

bwrigley

2 years ago

Level 5

@krisi_gjika ah that's brilliant! That would work beautifully I think. Especially as you say, I can collect all the comments to a certain depth in a single query.

I'll have to have a think of how to best handle that in Vue afterwards but there will be a straightforward way I'm sure.

Thanks so much!

1 like

ahmad_kash

2 years ago

Level 1

i think using scope query would solve your problem but i won't recommend doing that

use Illuminate\Support\Str;

public function scopeLoadComments(Builder $query, int $depth):Builder
    {
        $str =  Str::of('commentsRecursive.')->repeat($depth)->beforeLast('.');
        
        return $query->load($str);
    }

Please or to participate in this conversation.