Eager load the users with the most comments on each forum post

I am building a forum.

Each post preview on the posts index page should show the avatars of the 3 users with the most comments. The end result looks like this Tailwind UI component.

I have implemented a hideous, naive solution:

Step 1: Eager load user field of all comments

public function index()
{
    $posts = Post::with([
        'user',
        'topic',
        'comments:id,user_id,post_id',
        'comments.user',
    ])
        ->paginate();

    return Inertia::render('Posts', [
        'posts' => PostResource::collection($posts),
    ]);
}

Step 2: On the frontend, find the users with the most comments on each forum post.

function computeTopCommenters(post) {
    const nonOpComments = post.comments.filter(
        (comment) => comment.user.id !== post.user.id,
    );

    const commentersById = nonOpComments.reduce(
        (commentersById, comment) => ({
            ...commentersById,
            [comment.user.id]: comment.user,
        }),
        {},
    );

    const commentCountsByUserId = nonOpComments.reduce(
        (commentCountsByUserId, comment) => ({
            ...commentCountsByUserId,
            [comment.user.id]:
                (commentCountsByUserId[comment.user.id] ?? 0) + 1,
        }),
        {},
    );

    return Object.keys(commentCountsByUserId)
        .map((userId) => ({
            userId,
            commentCount: commentCountsByUserId[userId],
        }))
        .sort((a, b) => b.commentCount - a.commentCount)
        .slice(0, 3)
        .map(({ userId }) => commentersById[userId]);
}

I want to push this work to the database and avoid loading all comments into memory.

Is that possible with Eloquent relationships?

Posts:

Schema::create('posts', function (Blueprint $table) {
    $table->id();
    $table->foreignId('user_id')->constrained()->cascadeOnDelete();
    $table->foreignId('topic_id')->constrained()->cascadeOnDelete();
    $table->string('title');
    $table->timestamps();
});

class Post extends Model
{
    use HasFactory;

    public function user(): BelongsTo
    {
        return $this->belongsTo(User::class);
    }

    public function topic(): BelongsTo
    {
        return $this->belongsTo(Topic::class);
    }

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

Comments:

Schema::create('comments', function (Blueprint $table) {
    $table->id();
    $table->foreignId('user_id')->constrained()->cascadeOnDelete();
    $table->foreignId('post_id')->constrained()->cascadeOnDelete();
    $table->timestamps();
});

class Comment extends Model
{
    use HasFactory;

    public function user(): BelongsTo
    {
        return $this->belongsTo(User::class);
    }

    public function post(): BelongsTo
    {
        return $this->belongsTo(Post::class);
    }
}

LaryAI

2 years ago

Level 58

To achieve this in a more efficient manner, you can leverage Eloquent relationships and subqueries to perform the necessary calculations directly in the database. Here's a step-by-step solution:

Create a subquery to get the top commenters for each post.
Use a join to attach the top commenters to the posts.
Eager load the necessary relationships.

Here's how you can do it:

Step 1: Create a Subquery for Top Commenters

First, create a subquery to get the top 3 commenters for each post. This can be done using a raw SQL query or Eloquent's query builder.

Step 2: Attach Top Commenters to Posts

Next, join this subquery with the posts to get the top commenters for each post.

Step 3: Eager Load Relationships

Finally, eager load the necessary relationships to avoid the N+1 problem.

Here's the complete implementation:

use Illuminate\Support\Facades\DB;

public function index()
{
    // Subquery to get the top 3 commenters for each post
    $topCommentersSubquery = DB::table('comments')
        ->select('post_id', 'user_id', DB::raw('COUNT(*) as comment_count'))
        ->groupBy('post_id', 'user_id')
        ->orderBy('post_id')
        ->orderByDesc('comment_count')
        ->limit(3);

    // Join the subquery with the posts
    $posts = Post::with(['user', 'topic'])
        ->leftJoinSub($topCommentersSubquery, 'top_commenters', function ($join) {
            $join->on('posts.id', '=', 'top_commenters.post_id');
        })
        ->select('posts.*', 'top_commenters.user_id as top_commenter_user_id', 'top_commenters.comment_count')
        ->paginate();

    // Eager load the users for the top commenters
    $posts->load(['comments.user' => function ($query) {
        $query->whereIn('id', $posts->pluck('top_commenter_user_id')->unique());
    }]);

    return Inertia::render('Posts', [
        'posts' => PostResource::collection($posts),
    ]);
}

Explanation:

Subquery for Top Commenters:
- The subquery groups comments by post_id and user_id, counts the comments, and orders them by post_id and comment_count in descending order.
- The limit(3) ensures that we get the top 3 commenters for each post.
Join with Posts:
- The leftJoinSub method joins the subquery with the posts, attaching the top commenters to each post.
Eager Load Relationships:
- The load method is used to eager load the users for the top commenters, avoiding the N+1 problem.

This approach ensures that the heavy lifting is done by the database, reducing the amount of data loaded into memory and improving performance.

Please or to participate in this conversation.