Eager Loading with Nested Relationships and Custom Accessors Causing Performance Issues

I’m working on an application using Laravel 10, and I’m facing a performance issue related to eager loading nested relationships and custom accessors.

Scenario:
I have three models: User, Post, and Comment.

A User has many Posts.
A Post has many Comments.
I have set up a custom accessor on the Post model to calculate the number of comments.

Here are the relevant parts of my models:

// User.php
class User extends Model
{
    public function posts()
    {
        return $this->hasMany(Post::class);
    }
}

// Post.php
class Post extends Model
{
    protected $appends = ['comment_count'];

    public function comments()
    {
        return $this->hasMany(Comment::class);
    }

    public function getCommentCountAttribute()
    {
        return $this->comments()->count();
    }
}

// Comment.php
class Comment extends Model
{
    public function post()
    {
        return $this->belongsTo(Post::class);
    }
}

Problem:
In my controller, I’m trying to load all users with their posts and the number of comments on each post. Here’s how I’m doing it:

$users = User::with(['posts.comments'])->get();

The issue I’m facing is that this approach is causing a significant performance hit due to the N+1 query problem. The custom accessor getCommentCountAttribute is being called for each post, and it results in a new query being executed for each post’s comment count.

What I’ve Tried:

  1. Adding withCount: I tried to optimize it by using withCount in the controller like this:
$users = User::with(['posts' => function($query) {
    $query->withCount('comments');
}])->get();

This works, but the problem is that it doesn’t use the custom accessor, so the comment_count attribute in the Post model isn’t populated.

  1. Using loadMissing: I attempted to use loadMissing to avoid loading already loaded relations:
$users->loadMissing('posts.comments');

But this doesn’t solve the performance issue as it still leads to excessive queries.

  1. Avoiding Custom Accessors: I considered avoiding the custom accessor altogether, but the accessor is used in multiple places in the application, and refactoring it would be cumbersome.

Is there a way to keep using the custom accessor while avoiding the N+1 query issue? How can I optimize this eager loading to reduce the number of queries while still getting the comment_count for each post efficiently? Any insights or alternative approaches would be greatly appreciated!