skip to content

With Eloquent, what do withCount(), withSum() and withExists() add to a query, and how are the resulting attributes named?

level: middleimportance: should knowfreq 52%

answer

  1. correlated subselect in SELECT
  2. {relation}_count, no models loaded
  3. {relation}_sum_{column}
  4. withExists cast to boolean
  5. select() after withCount drops it

basics

~20 s

They add a correlated subselect to the SELECT list, so each parent row comes back with an aggregate attribute and no related models are loaded: tags_count, purchases_sum_amount, or a boolean comments_exists. An 'as' alias renames it.

solid answer

~40 s

`Photo::withCount('tags')` adds `(SELECT COUNT(*) FROM tags JOIN photo_tag ... WHERE photos.id = photo_tag.photo_id) AS tags_count` to the photo query. Each photo gets a `tags_count` attribute and no `Tag` models are hydrated. The other aggregates follow the `{relation}_{function}_{column}` pattern: `withSum('purchases', 'amount')` gives `purchases_sum_amount`, and `withMin`, `withMax` and `withAvg` work the same way. `withExists('comments')` gives `comments_exists`, cast to a boolean. An array entry such as `'comments as flagged_count' => fn ($q) => $q->where('flagged', true)` adds constraints and an alias. If the query has no columns yet, it selects `photos.*` first, so call `select()` before `withCount()`: `select()` resets the column list and would drop the subselect.

code

php · 19 lines
php
<?php

use App\Models\Photo;

$photos = Photo::select('id', 'title', 'user_id')
    ->withCount([
        'tags',
        'comments as flagged_count' => fn ($q) => $q->where('flagged', true),
    ])
    ->withSum('purchases', 'amount')
    ->withExists('comments')
    ->orderByDesc('tags_count')
    ->paginate(24);

foreach ($photos as $photo) {
    echo $photo->tags_count, ' ', $photo->flagged_count, ' ';
    echo $photo->purchases_sum_amount ?? 0, ' ';
    echo $photo->comments_exists ? 'has comments' : 'quiet';
}

go deeper

for a junior

Recall that withCount('tags') puts a tags_count attribute on each model without loading the tags.

for a middle

Explain the correlated subselect, the relation_function_column naming, aliases with constraints, and the select() ordering rule.

for a senior

Choose aggregates over loading on list pages, spot where() on an alias and null sums in reviews, and check the subselect is indexed.

for a principal

Decide when per-row aggregate subselects are enough and when a stored counter or summary table is worth its write-path cost.

## The problem these methods solve A gallery page shows each photo with "12 tags", "3 comments" and "prints sold: 41.50". Loading every tag and comment just to count them wastes memory, and counting per photo in a loop runs one query per photo. Eloquent's **relationship aggregate** methods ask the database for the number in the **same query** that fetches the photos. ## What they add to the SQL Each call adds a **correlated subselect** to the `SELECT` list of the parent query: ```sql select photos.*, (select count(*) from tags inner join photo_tag on tags.id = photo_tag.tag_id where photos.id = photo_tag.photo_id) as tags_count from photos ``` The value arrives as a normal attribute on each `Photo` model. **No related models are loaded**; `$photo->tags` is still unloaded afterwards. ## The methods and their attribute names | Call | Attribute on each photo | Value when there are no related rows | |---|---|---| | `withCount('tags')` | `tags_count` | `0` | | `withSum('purchases', 'amount')` | `purchases_sum_amount` | `null` (SQL `SUM` over no rows) | | `withAvg('ratings', 'stars')` | `ratings_avg_stars` | `null` | | `withMin` / `withMax('purchases', 'amount')` | `purchases_min_amount` / `purchases_max_amount` | `null` | | `withExists('comments')` | `comments_exists`, cast to `bool` | `false` | The framework builds the default name by snake-casing "relation function column", which is why `withCount` ends in `_count`. All of them are thin wrappers over `withAggregate($relation, $column, $function)`. ## Constraints and aliases Pass an array to constrain the subselect or to count the same relation twice: - `withCount(['comments', 'comments as flagged_count' => fn ($q) => $q->where('flagged', true)])` gives both `comments_count` and `flagged_count`. - `withSum('purchases as revenue', 'amount')` renames the sum to `revenue`. - Constraints written in the relation method and the related model's global scopes (soft deletes, for instance) apply inside the subselect, just as they do for `whereHas`. ## Pitfalls interviewers look for 1. **Order with select().** If the query has no explicit columns, `withAggregate` first selects `photos.*`. A later `select('id', 'title')` resets the column list and silently drops the subselect. Call `select()` first, then `withCount()`, or use `addSelect()`. 2. **Filtering on the alias.** `->where('tags_count', '>', 3)` fails, because SQL's `WHERE` cannot see aliases from the `SELECT` list. Filter with `has('tags', '>', 3)` instead; ordering with `orderBy('tags_count', 'desc')` does work. 3. **Null sums.** `purchases_sum_amount` is `null`, not `0`, for a photo with no purchases; format it with a fallback. 4. **Counting already-loaded models.** These methods work on a query. For models you already hold, a separate method counts after the fact; that belongs with eager loading. ## When to prefer them - A list page that shows counts but not the rows: `withCount` is one query and no model hydration. - A badge such as "has comments": `withExists` returns a boolean and lets the database stop at the first row. - A dashboard of totals per parent: `withSum` and friends keep the arithmetic in SQL. If you also need the related rows themselves, you are loading them anyway, and counting the loaded collection may be enough. ## Cost compared with the alternatives | Approach for 24 photos on a page | Queries | Models hydrated | |---|---|---| | `$photo->tags()->count()` inside the loop | 1 + 24 | 24 photos | | `$photo->tags->count()` inside the loop | 1 + 24 | 24 photos plus every tag | | `Photo::withCount('tags')` | 1 | 24 photos | The subselect is still evaluated per row by the database, so it relies on an index on the pivot's `photo_id`, but it avoids both the round trips and the PHP memory.

  • How do you list only photos with more than three tags, sorted by tag count?
    Filter with `has('tags', '>', 3)`, which adds a COUNT subquery to the `WHERE` clause, then add `withCount('tags')` and `orderByDesc('tags_count')` to expose and sort by the number. A `where('tags_count', '>', 3)` fails because `WHERE` cannot reference a `SELECT` alias.
  • Why does withExists() return a boolean when withCount() returns a number?
    `withExists` compiles to `exists(subquery) as comments_exists` and registers a `bool` cast for that alias with `withCasts()`, so the attribute reads as `true` or `false`. `withCount` compiles to a `COUNT(*)` subselect with no cast added.

saying these in an interview costs you the question

  • Thinking withCount() loads the related models and counts them in PHP
  • Expecting withSum() to return 0 when there are no related rows
  • Filtering with where('tags_count', ...) on the aggregate alias
  • Calling select() after withCount() and expecting the count to survive
  • Guessing the attribute is named count_tags or tags_total