Categories

Eloquent Aggregates in Laravel: Powerful Reporting with withSum, withAvg, and Subqueries

Donate with

Donate BAT now

Verified BAT publisher

Introduction

Modern applications rely heavily on reporting, analytics dashboards, aggregated statistics, and data summaries. Laravel’s Eloquent ORM provides several built-in tools to make these operations efficient and expressive — without requiring heavy SQL or complex manual joins. Among the most powerful features are the Eloquent aggregate methods such as withSum, withAvg, withCount, and custom subquery columns.

These features allow you to enrich your models with calculated values directly from the database, offering a clean and high-performance approach to data reporting. In this article, we explore how to use Eloquent aggregates effectively, walk through practical use cases, discuss dependencies and configuration, and compare these techniques against manual SQL or third-party reporting packages.

Why Eloquent Aggregates Matter

Performing aggregate calculations such as totals, averages, or counts is common in almost every application. Traditionally, developers might write raw SQL or execute multiple queries to gather the data they need. This approach leads to:

• Multiple queries and poor performance
• Duplicate logic across reports
• Complex controller code with manual joins
• Inconsistent aggregate values across different endpoints

Eloquent aggregates solve these problems by allowing you to attach calculated values directly to your models as part of the query itself, not after execution.

Use Cases: When to Use Eloquent Aggregates

Aggregates are especially useful when you need to return summarized information alongside your models. Common real-world examples include:

1. Total order value per customer using withSum
2. Average rating for products using withAvg
3. Counting related records using withCount
4. Sales reports with month or year-based aggregates
5. User activity summaries with subqueries
6. Nested statistics for dashboards without additional queries

Eloquent makes these easy to implement while maintaining readable code.

Dependencies and Setup

Eloquent aggregates require no additional setup — they are part of Laravel’s core ORM.

Supported methods include:

• withCount()
• withSum()
• withAvg()
• withMin()
• withMax()

These can also be nested, conditional, or combined with subquery columns.

Using withCount() for Relationship Counts

One of the most common aggregate operations is counting related records:

$users = User::withCount('posts')->get();

This adds a new attribute:

$user->posts_count

For multiple counts:

$users = User::withCount(['posts', 'comments'])->get();

Alias the result:

$users = User::withCount(['posts as article_count'])->get();

Using withSum() for Totals

Suppose a customer has many orders:

$customers = Customer::withSum('orders', 'total_amount')->get();

This adds:

$customer->orders_sum_total_amount

You can also alias it for readability:

$customers = Customer::withSum('orders as total_spent', 'total_amount')->get();

Using withAvg() for Averages

To calculate the average rating of a product:

$products = Product::withAvg('reviews', 'rating')->get();

This adds:

$product->reviews_avg_rating

If no related records exist, the value will be null.
Alias example:

$products = Product::withAvg('reviews as avg_rating', 'rating')->get();

Combining Multiple Aggregates

You can add several aggregate functions in a single query:

$products = Product::query()
    ->withCount('reviews')
    ->withAvg('reviews as avg_rating', 'rating')
    ->withSum('sales as revenue', 'amount')
    ->get();

This returns fully enriched models ready for dashboards or reporting screens.

Advanced Techniques with Subquery Columns

While withSum(), withAvg(), and withCount() cover most cases, sometimes you need more complex calculations. Laravel supports adding subquery columns directly to your select statement.

Example: Latest Order Date for Each Customer

$customers = Customer::query()->addSelect([
    'last_order_date' => Order::select('created_at')
        ->whereColumn('customer_id', 'customers.id')
        ->latest()
        ->limit(1)
])->get();

Example: Total Sales for the Current Month

$customers = Customer::query()->addSelect([
    'current_month_sales' => Order::selectRaw('SUM(total_amount)')
        ->whereColumn('customer_id', 'customers.id')
        ->whereMonth('created_at', now()->month)
])->get();

These subquery techniques give you SQL-level power while keeping your queries expressive and readable.

Conditional Aggregates

Aggregate queries can include conditional logic.

For example, count only “approved” comments:

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

Sum only “completed” orders:

$customers = Customer::withSum([
    'orders as completed_total' => function ($query) {
        $query->where('status', 'completed');
    }
],
'total_amount')->get();

Performance Considerations

Eloquent aggregates are generally fast because they are executed by the database engine. Still, keep in mind:

• Aggregates commonly rely on correlated subqueries, which are usually efficient but can become expensive on very large datasets.
• Indexes on foreign keys dramatically improve speed.
• Limit nested aggregates when dealing with large relationship graphs.
• For dashboards with heavy traffic, consider caching aggregated values.

Comparison with Alternative Approaches

Conclusion

Eloquent aggregates offer a powerful and elegant way to enrich your models with statistical or analytical insights. Whether you are building dashboards, analytics tools, customer summaries, or reporting modules, these aggregate functions allow you to create high-performance, expressive, and maintainable queries with minimal effort.

By leveraging withSum, withAvg, withCount, and subquery columns, you can transform your Laravel application into a clean, fast, and analytics-ready system — all without dropping down to raw SQL.