Laravel using where clause on a withCount method

Laravel using where clause on a withCount method

  1. Blog
  2. 15 November 2026
  3. 3 min read

Stefan Bogdanescu

Founder & Senior Architect

Filtering Eloquent Collections by Aggregated Counts

Using withCount in Laravel allows you to eagerly load relationship counts directly onto your model instances without writing a separate query for the aggregation. This is useful when you need the count data in your serialized JSON or view context. However, applying a where clause on that aggregated column immediately after calling withCount often triggers SQL errors because of how Laravel constructs the subquery.

The error message Unknown column 'upvotes_count' in 'where clause' typically stems from the order of operations in the generated SQL. When you chain .where('upvotes_count', '>', 5) after .withCount('upvotes'), Laravel attempts to filter the results based on a column that exists only within an aggregate subquery, which standard SQL does not support in the outer query's WHERE clause without specific syntax.

How Laravel Generates the Query

When you execute:

$posts = Post::withCount('upvotes')->where('upvotes_count', '>', 5)->get();

Laravel generates a query that looks conceptually like this:

SELECT posts.*, (SELECT COUNT(*) FROM upvotes WHERE ...) AS upvotes_count 
FROM posts 
WHERE upvotes_count > 5

In standard SQL, you cannot reference an alias defined by an aggregate function in the WHERE clause of the same query. To filter on aggregated data, you must use a subquery or a HAVING clause, which Laravel handles differently depending on the method used.

The Correct Approach: Using having

If your goal is to filter the parent model based on the count of related records returned by an aggregate, you should use the having clause instead of where. The withCount method returns the count as a property on the model, but to filter the query builder results before fetching them, having is the appropriate tool.

You can modify the query like this:

$posts = Post::withCount('upvotes')
    ->having('upvotes_count', '>', 5)
    ->get();

This generates SQL that correctly filters based on the aggregated count in a subquery context:

SELECT posts.*, (SELECT COUNT(*) FROM upvotes WHERE ...) AS upvotes_count 
FROM posts 
HAVING upvotes_count > 5

Filtering with Custom Column Names

Sometimes, you might want to filter by a specific column name that matches the alias Laravel assigns. In most cases, Laravel automatically uses the relationship name followed by _count (e.g., upvotes_count). However, if you need more control or are working with a custom alias, you can specify it explicitly in the having clause:

$posts = Post::withCount(['upvotes' => 'vote_total'])
    ->having('vote_total', '>', 5)
    ->get();

This approach ensures that your filter targets the correct column alias regardless of what name you assign to the count.

Avoiding Common Pitfalls

It is important not to attempt filtering inside the relationship closure when trying to apply conditions on the aggregate result. For example:

Post::where('id', $id)
    ->withCount(['upvotes' => function($query) {
        $query->where('upvotes_count', '>', 5); // This will fail
    }])
    ->get();

This fails because upvotes_count is not a column in the upvotes table; it is an alias assigned to the result of the count operation. Placing conditions on that alias inside the relationship closure creates a forward reference error, as the database cannot resolve the alias before executing the aggregation.

Similarly, manipulating the inner query to select a custom alias and then filtering on it in the outer query often leads to confusion. If you want to filter based on the count, keep the logic in the main query using having, rather than trying to reshape the relationship's internal query structure for that purpose.

When to Use has Instead

If your requirement is simply to find posts that have more than five upvotes rather than fetching the exact count alongside the filter, you might consider using the has method combined with a nested query:

$posts = Post::whereHas('upvotes', function($query) {
    $query->withCount()
          ->havingRaw('count(*) > ?', [5]);
})->get();

While this approach is more verbose, it avoids the complexity of managing aggregate aliases in the outer query. However, if you need the count value for rendering views or JSON responses, withCount combined with having remains the most efficient and readable solution.

Our network