Laravel - N+1 problem within my relationship recursion

Question

I have a laravel website that I'm building. It has submissions, and submissions have comments. I want to eager-load these comments AND its child comments (and those comment's children, etc). But only up to a point.

The child comment loop only loads child comments within a $loop->depth of 10, and a $loop->iteration of 8. I don't want to eager load comments I won't even be displaying.

This is what I have so far:

Controller:

$repliesCount = Comment::with([
            'owner',
            'savedComments',
            'votes',
        ])
        ->where('submission_id', $submission->id)
        ->whereNotNull('parent_id')
        ->count();


$comments = Comment::where('submission_id', $submission->id)
    ->whereNull('parent_id')
    ->with([
        'owner',
        'savedComments',
        'votes',
        'submission',
        'reports'
    ])
    ->orderBy('removed', 'asc')
    ->orderBy($sortBy, $direction)
    ->paginate(100);

$replies = Comment::where('submission_id', $submission->id)
            ->whereNotNull('parent_id')
            ->with([
                'owner',
                'savedComments',
                'votes',
                'submission',
                'reports'
            ])
            ->orderBy('removed', 'asc')
            ->orderBy($sortBy, $direction)
            ->paginate($repliesCount);
            
$comments_by_id = new Collection();
$replies_by_id = new Collection();
foreach ($comments as $comment) {
    $comments_by_id->put($comment->id, $comment);
    $comments_by_id->get($comment->id)->children = new Collection();
}
foreach ($replies as $reply) {
    $replies_by_id->put($reply->id, $reply);
    $replies_by_id->get($reply->id)->children = new Collection();
}
foreach ($replies as $key => $reply) {
    if ($comments_by_id->get($reply->parent_id)) {
        $comments_by_id->get($reply->parent_id)->children->push($reply);
    } elseif ($replies_by_id->get($reply->parent_id)) {
        $replies_by_id->get($reply->parent_id)->children->push($reply);
    }
}

Blade:

@foreach ($comment->children as $comment)
    @if ($loop->depth == 10)
        <div>
            <a href="{{ route('get.submission', ['subchan' => $comment->submission->subchan, 'id' => $comment->submission->id, 'URLtitle' => $comment->submission->URL_title,'commentID' => $comment->parent_id]) }}">Continue this thread</a>
        </div>
        @break
    @endif
    <div class="comment-container comment-container-child" id="comment-container-{{$comment->id}}" hidden-level="{{ceil(($loop->iteration + 3) / 10) - 1}}">
        @include('partials.comment_block')
    </div>
    @if ($loop->iteration % 10 == 7 && $loop -> remaining > 0)
        
        <div class="loadMoreReplies comment-container-child load-more"
        hidden-level="{{ceil(($loop->iteration + 3) / 10) - 1}}"
        data-submission-id="{{ $comment->submission->id }}"
        data-parent-id="{{$comment->parent_id}}"
        
        >Load More Replies (<span id="remaining-reply-count-{{$comment->parent_id}}">{{ $loop->remaining }}</span>)</div>
    @endif
@endforeach

Basically, I take 100 comments ($comments), and I eager load the relationships that I need (for example the "owner" relationship).

Then I make a query to get all children comments for that submission ($replies) and all its relationships.

After that I create a collection that will push every child reply into its parent comment->children.

This works. However, it loads ALL children replies for a given comment. So even though I only load 100 parent comments, if one of those comments or child comments has THOUSANDS of child comments it loads them all in one go.

Here is an image to better illustrate (the 5000 replies are hidden but are still getting queried so the page load takes a long time):

What I ultimately want is that it only eager loads and returns a max of 10 child comments, while also passing how much children a comment has so I can display how many children is missing.

Answer 1

its a bit complicated. you need two relationship for children one gets only 10 records and the other gets all(by default). so i assume you eager load all comments in model by $with property like $with = ['children'];. instead you do this in comment model:

class Comment extends Model
{
    protected $with = ['tenChildren'];

    public function tenChildren()
    {
        // fill others params if needed
        // sort it or whatever
        return $this->hasMany(Comment::class)->limit(10);
    }

    public function children()
    {
        return $this->hasMany(Comment::class);// fill others params if needed
    }
}

so the first time only parents with 10 children received. then you just need get the remained children from backend in here you have a bit problem. with js modern frameworks that have states(to detect how many children been received) made it easy. but to get extra children just need a route to get children in paginated data. for e.x to get other children for second comment you need send get req to this add /api/comments/2?page=2.