Model::withCount

Adds a {relation}_count attribute via a subselect without loading any of the related rows.

Signature
public function withCount(mixed $relations): $this

Parameters

ParameterTypeDescription
$relationsmixedA relationship name, an array of names, or an array keyed by name (optionally with an 'as alias' suffix) whose values are Closures constraining the subselect. Multiple names may also be passed as separate variadic arguments.

Return value

$thisThe same builder instance. Each retrieved model gains a {relation}_count attribute holding the aggregate.

Errors

Throws Illuminate\Database\Eloquent\RelationNotFoundException when a named relationship is not defined on the model.

See also

Model::withModel::whereHasModel::orderByModel::where

withCount() adds a correlated subselect to the parent query for each named relationship and exposes the result as a {relation}_count attribute. Post::withCount('comments') gives every post a comments_count without a single comment row being fetched.

When to reach for it

Any list that shows "12 comments" or "3 items". The alternative — eager loading the relation just to call count() on it — pulls every related row across the wire and into PHP memory to produce one integer.

Aliases and constraints

The array form takes a Closure to constrain the subselect, and an as suffix to name the attribute. That lets one query carry several differently-filtered counts of the same relationship — 'comments as pending_comments_count' => fn ($q) => $q->where('approved', false) alongside a plain 'comments'.

What trips people up

Order matters with select(). The documentation is explicit: call withCount after select, otherwise the explicit select list replaces the subselect and the attribute never appears.

The count arrives as whatever the driver returns — often a string on MySQL rather than an int. Compare with ==, cast it, or declare an int cast on the attribute before doing arithmetic or a strict comparison.

It counts, it does not filter. A post with zero comments still comes back with comments_count of 0; use has() or whereHas() to exclude it.

Example

Example · php
<?php

use App\Models\Post;
use Illuminate\Database\Eloquent\Builder;

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

foreach ($posts as $post) {
    echo $post->title, ': ', $post->comments_count, "\n";
}

// Several relationships, one of them constrained.
$posts = Post::withCount([
    'votes',
    'comments' => function (Builder $query) {
        $query->where('content', 'like', 'code%');
    },
])->get();

echo $posts[0]->votes_count, "\n";
echo $posts[0]->comments_count, "\n";

When to use it

  • Show a comment tally next to each post in an index without loading the comments.
  • Sort a leaderboard by how many entries each user submitted.
  • Display approved and pending counts for the same relationship side by side.

Frequently asked questions

Why is my {relation}_count attribute missing?

Usually because select() was called after withCount(), which replaces the select list and drops the subselect column. Reorder so withCount() comes last, as the documentation specifies. The other cause is a mismatched name: the attribute is derived from the relationship method name in snake_case, so a camelCase relationship such as blogComments becomes blog_comments_count.

Does withCount() filter out models with zero related rows?

No — it only adds a number. A post with no comments is still returned, with comments_count set to 0. To exclude those rows use has('comments') or whereHas('comments', $callback), which compile to EXISTS subqueries; the two are commonly chained together so you get both the filter and the tally in one query.

Why is comments_count a string instead of an integer?

The value comes straight from the driver, and MySQL's PDO driver returns aggregate columns as strings unless native types are enabled. Cast explicitly with (int), compare loosely, or add 'comments_count' => 'integer' to the model's $casts array so the attribute is converted on access — important if you use === or feed the value into arithmetic.

More examples

Aliasing several counts of one relationship

Two subselects over the same relationship coexist because the alias renames the second attribute. The explicit casts matter: MySQL returns these as strings, so subtracting them without casting relies on PHP's coercion and a === comparison against an integer would fail.

Example · php
<?php

use App\Models\Post;
use Illuminate\Database\Eloquent\Builder;

$posts = Post::withCount([
    'comments',
    'comments as pending_comments_count' => function (Builder $query) {
        $query->where('approved', false);
    },
])->get();

echo $posts[0]->comments_count, "\n";           // e.g. 12
echo $posts[0]->pending_comments_count, "\n";   // e.g. 3

// The counts come back as driver-native scalars - cast before arithmetic.
$approved = (int) $posts[0]->comments_count
          - (int) $posts[0]->pending_comments_count;

echo $approved, "\n";                            // 9

Ordering by a relationship count

Because withCount adds a real select alias, the database can sort by it — sorting the collection in PHP would only order the ten rows you already limited to. The name tie-break keeps the output stable when several users share a count. The second query shows that has() does the filtering; withCount alone never removes a row.

Example · php
<?php

use App\Models\User;

// The subselect alias is a real column in the outer query.
$leaders = User::withCount('submissions')
    ->orderByDesc('submissions_count')
    ->orderBy('name')          // stable tie-break
    ->limit(10)
    ->get();

foreach ($leaders as $user) {
    echo $user->name, ' ', $user->submissions_count, "\n";
}

// Counting and filtering are separate concerns.
$active = User::withCount('submissions')
    ->has('submissions', '>=', 5)
    ->get();

echo $active->count(), "\n";

select() ordering and the deferred alternative

The first query prints a real number; the commented one would raise an undefined-property style failure because the later select() discarded the count column. loadCount() is the after-the-fact equivalent, useful when you only decide you need the tally once the model is in hand.

Example · php
<?php

use App\Models\Post;
use App\Models\Book;
use Illuminate\Database\Eloquent\Builder;

// Correct: withCount after select.
$posts = Post::select(['id', 'title', 'body'])
    ->withCount('comments')
    ->get();

echo $posts[0]->comments_count, "\n";

// Wrong order - the select replaces the subselect, attribute is missing.
// Post::withCount('comments')->select(['id', 'title'])->get();

// Already have the model? loadCount() runs the aggregate separately.
$book = Book::first();

$book->loadCount('genres');
echo $book->genres_count, "\n";

$book->loadCount(['reviews' => fn (Builder $q) => $q->where('rating', 5)]);
echo $book->reviews_count, "\n";

Related

eloquent witheloquent wherehaseloquent orderbyeloquent whereeloquent all

Discussion

  • Be the first to comment on this lesson.