Counting Related Records in Laravel Without N+1 Queries

Listing posts with a comment total looks trivial until the page loads. The first request fetches posts, then the view asks for each post’s comment count, triggering one extra query per row. That N+1 pattern is easy to write and hard to notice until a dashboard slows down.

The useful takeaway is to let Eloquent compute the counts in the original query with withCount. It adds a relation_count attribute to each model without loading the related rows, keeping the list fast and the code readable.

Why counting in a loop hurts listing pages

Eloquent makes lazy loading convenient. Accessing $post->comments->count() inside a Blade loop will run a SELECT * FROM comments WHERE post_id = ? for every post. On a page with 50 posts that is 51 queries.

Eager loading with with('comments') avoids the per-row queries but still loads every comment row into memory. For a count you do not need the rows, only the number.

How withCount builds a single query

withCount appends a subselect for each relation and selects the result as an attribute. The relation is not loaded, only the aggregate.

For a Post model with a comments() hasMany relation, the call is:

use App\Models\Post;

$posts = Post::withCount('comments')->get();

Each $post now has $post->comments_count. The attribute name is the snake_case plural of the relation, with _count appended. You can override it with as:

Post::withCount(['comments as approved_comments_count' => function ($q) {
    $q->where('approved', true);
}])->get();

Constraints are applied inside the closure, and you can combine withCount with with when you need both the count and a limited set of related rows.

A minimal blog example with constraints

Assume two models. Post defines:

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

Comment belongs to Post. In a controller you can return a list ready for a table or API:

public function index()
{
    $posts = Post::query()
        ->withCount([
            'comments',
            'comments as approved_comments_count' => fn($q) => $q->where('approved', true),
        ])
        ->with('author:id,name')
        ->orderByDesc('created_at')
        ->paginate(20);

    return view('posts.index', compact('posts'));
}

In the view you reference the attributes directly:

{{ $post->title }} — {{ $post->comments_count }} comments

To check that only one query is used, enable the query log in a non-production environment and run the query in Tinker. From the project root, run php artisan tinker as a user with read access to the project. Then execute:

DB::enableQueryLog();
Post::withCount('comments')->limit(5)->get();
collect(DB::getQueryLog())->pluck('query');

You should see a single SELECT with a subselect for the count. Each model should expose a comments_count attribute matching the number of related rows.

Trade-offs and limitations

withCount adds extra columns to the select list. It is not suitable for aggregates like sum or average; for those use a custom select or a database view.

The generated column name follows the relation name. Renaming with as helps readability but can be confusing when the alias is used later in ordering or filtering.

Soft deletes are respected only when the related model uses the SoftDeletes trait. If the trait is missing, trashed rows are counted. Verify the model configuration before relying on the count for moderation UIs.

Because the count is computed in SQL, it reflects the database state at query time. If you need a real-time count after a write in the same request, re-query or refresh the model.

When to use it

Use withCount for listing pages, dashboards, and sorting by related totals where you need the number but not the rows. Keep the relation constraints narrow, avoid selecting unnecessary columns, and test the query log on a representative data set to confirm a single round trip.