How to get nested relation count?

[mansoorkhan96]

Hi, I need some help with counting nested relations.

I am using Spatie comments package, with a feature allowing nested comments.

I have a model called Activity which has comments and i need to get all the comments and also count all the comments including the nested comments.

Looking for somehthing:

Activity::with('allComments')->withCount('allComments');

class Activity extends Model
{
    public function comments()
    {
        return $this->morphMany(Comment::class, 'commentable');
    }
}

class Comment extends Model
{
    use HasRecursiveRelationships;

    public function nestedComments(): HasMany
    {
        return $this->hasMany(Comment::class, 'parent_id');
    }
}

I have added allComments method in Activity model but it runs into sql errors.

public function allComments()
    {
        return $this->hasManyDeep(
            Comment::class,
            [Comment::class.' as parent'],
            [
                'commentable_id',
                'id',        
                'parent_id', 
            ],
            [
                'id',  
                'commentable_id', 
                'id',    
            ]
        );
    }

[mansoorkhan96]

Also trying this but still get zero results.

public function allComments()
    {
        return $this->hasManyDeepFromRelations(
            $this->comments(),
            (new Comment())->setAlias('nested_comments')->nestedComments()
        );
    }

And sql query becomes following which is not quite right... I think i need to look into recursive queries to make this possible.

select *
FROM
	`comments`
	INNER JOIN `comments` AS `nested_comments` ON `nested_comments`.`id` = `comments`.`parent_id`
WHERE
	`nested_comments`.`commentable_id` = 8010
	AND `comments`.`commentable_type` = 'App\\Models\\Activity'

[staudenmeir]

Hi @mansoorkhan96,
How deep can the comments be nested? Is there a limit?

[mansoorkhan96]

Its 2 levels deep.
An Activity can have many main/top level comments and then a main/top level comment can have many nested comments. thats it. Nested comments cannot have more comments, only a top level comment can have many nested comments.

Activity
       -> main comment
              ->nested comment
              ->nested comment
       -> main comment
              ->nested comment
              ->nested comment

[mansoorkhan96]

@staudenmeir Please let me know if a DB schema would help. thanks.

[staudenmeir]

This is a bigger topic:
The package can't fetch comments from multiple levels at once. Your allComments relationship can only return second-level comments and your approach with hasManyDeepFromRelations() is correct for this use case. The empty result is actually a bug that I just fixed.

You can use one of my other packages to get all comments with a single relationship that you can use with withCount():
https://github.com/staudenmeir/laravel-merged-relations

Update this package to get the bug fix, install the other package and set it up:

class Activity extends Model
{
    use \Staudenmeir\LaravelMergedRelations\Eloquent\HasMergedRelationships;
    use HasRelationships;

    public function comments()
    {
        return $this->morphMany(Comment::class, 'commentable');
    }

    public function nestedComments()
    {
        return $this->hasManyDeepFromRelations(
            $this->comments(),
            (new Comment())->setAlias('nested_comments')->nestedComments()
        );
    }

    public function allComments()
    {
        return $this->mergedRelationWithModel(Comment::class, 'all_comments');
    }
}

\Staudenmeir\LaravelMergedRelations\Facades\Schema::createMergeView(
    'all_comments',
    [(new Activity())->comments(), (new Activity())->nestedComments()]
);

Then Activity::with('allComments')->withCount('allComments'); should work as expected.

[mansoorkhan96]

Appreciate your help.
Let me try this.

[mansoorkhan96]

@staudenmeir This worked like a charm!

This is next level.

Thank you so much...❤️

[mansoorkhan96]

You are a genius and all those eloquent package are masterpiece.