Eager loading polymorphic relationship problem

Hi all,

I want to create a livewire component that loads all comments belonging to a given article or video. For this purpose I use a polymorphic relationship. The comments are nested. Every comment can have n+1 level replies. When I try to retrieve all comments for an article I run into n+1 problem. What am I doing wrong?

Below is my code:

Livewire Component

class CommentsList extends Component
{
    public $article;

    #[Computed]
    public function comments()
    {
        
        return $this->article->comments()
                       ->with(['replies', 'owner'])
                            	->whereNull('parent_id')
                                ->orderBy('created_at', 'desc')
                                ->get();
     }
}

I also tried to use morphWith as shown in the documentation but without success.

Comment Model:

class Comment extends Model
{ 
    public function commentable(): MorphTo
    {
        return $this->morphTo();
    }

    public function owner()
    {
        return $this->belongsTo(User::class, 'user_id');
    }

    public function replies()
    {
        return $this->hasMany(self::class, 'parent_id');
    }
}

Article Model:

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

LaryAI

2 years ago

Level 58

To address the N+1 problem in your polymorphic relationship, you need to ensure that you are eager loading the nested relationships properly. The with method is used for eager loading, but it seems like you might need to use nested eager loading to handle the replies of the comments.

Here's how you can modify your comments method in the Livewire component to properly eager load the nested replies:

class CommentsList extends Component
{
    public $article;

    #[Computed]
    public function comments()
    {
        return $this->article->comments()
            ->with(['replies.owner', 'owner'])
            ->whereNull('parent_id')
            ->orderBy('created_at', 'desc')
            ->get();
    }
}

In this code, with(['replies.owner', 'owner']) ensures that both the replies and their owner relationships are eager loaded, which should help mitigate the N+1 query problem.

Additionally, make sure your Comment model's replies relationship is correctly defined to handle nested replies:

class Comment extends Model
{ 
    public function commentable(): MorphTo
    {
        return $this->morphTo();
    }

    public function owner()
    {
        return $this->belongsTo(User::class, 'user_id');
    }

    public function replies()
    {
        return $this->hasMany(self::class, 'parent_id')->with('replies');
    }
}

By adding ->with('replies') to the replies relationship, you ensure that replies are eager loaded recursively, which can help in reducing the number of queries executed.

Finally, ensure your Article model's comments relationship is correctly defined:

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

With these changes, you should be able to load all comments and their nested replies efficiently, reducing the N+1 query problem.

Ridderspoor

2 years ago

Level 6

Thanks to Lary's suggestion I have improvement in my results.

Now for every level of depth of replies I have a query like this:

//first level
select * from `comments` where `comments`.`parent_id` in (1, 2, 7, 10) 

//second//level
select * from `comments` where `comments`.`parent_id` in (3, 4, 8, 11)

//third level
select * from `comments` where `comments`.`parent_id` in (5)

Is it possible the code to be optimized and to have just one query like :

select * from `comments` where `comments`.`parent_id` in (1, 2, 7, 10, 3, 4, 8, 11, 5)

Please or to participate in this conversation.