Aggregate Laravel Package

Getting Started

Minimal Steps

1. Installation: Run composer require watson/aggregate in your Laravel project. No additional configuration is required due to Laravel's autodiscovery feature.
2. First Use Case: Replace withCount() with withSum(), withAvg(), withMin(), or withMax() for relationships where you need aggregated values (e.g., total revenue, average rating, or max price).

   $users = User::withSum('orders', 'amount')->get();
   $users->first()->orders_sum; // Access the aggregated value
   

Where to Look First

• README: Focus on the Usage section for syntax and examples.
• Laravel Docs: Reference Eloquent Relationships for withCount() behavior, as aggregate extends this functionality.
• Tests: Browse the test files for edge cases (e.g., joins, scopes, or complex queries).

Implementation Patterns

Core Workflows

1. Basic Aggregation: Replace withCount() with aggregate methods for relationships:

   // Count vs. Sum
   $posts = Post::withCount('comments')->get(); // comments_count
   $posts = Post::withSum('comments', 'likes')->get(); // comments_sum
   

2. Aliasing Aggregates: Use as to customize attribute names (avoid naming conflicts):

   $products = Product::withSum('reviews as avg_rating', 'rating')->get();
   $products->first()->avg_rating; // Access via alias
   

3. Combining Aggregates: Chain multiple aggregates in a single query:

   $orders = Order::withCount('items')
                 ->withSum('items as total_value', 'price')
                 ->withAvg('items as avg_price', 'price')
                 ->get();
   

4. Conditional Aggregation: Use query scopes or closures to filter aggregated data:

   $activeUsers = User::where('active', true)
                       ->withSum('orders as total_spent', 'amount')
                       ->get();
   

5. Joins with Aggregates: Combine aggregates with joins (e.g., for multi-table relationships):

   $posts = Post::join('comments', 'posts.id', '=', 'comments.post_id')
                ->withSum('comments as total_likes', 'likes')
                ->select('posts.*')
                ->get();
   

Integration Tips

• Eager Loading: Always eager-load aggregates to avoid the N+1 problem:

  $users = User::withSum('orders', 'amount')->get(); // Eager-loaded
  

• API Responses: Use with() to include aggregates in API responses:

  return User::with(['orders' => function ($query) {
      $query->select('user_id', \DB::raw('SUM(amount) as total'));
  }])->get();
  

• Caching: Cache aggregated results for performance:

  $cachedUsers = Cache::remember('users_with_aggregates', now()->addHours(1), function () {
      return User::withSum('orders', 'amount')->get();
  });
  

Gotchas and Tips

Pitfalls

1. Column Existence:

   • Error: SQLSTATE[42S22]: Column not found if the aggregated column doesn’t exist in the related table.
   • Fix: Verify column names (e.g., withSum('products', 'price') requires a price column in the products table).

2. Reserved Keywords:

   • Error: Using reserved SQL keywords (e.g., order, group) as column names may break queries.
   • Fix: Alias the aggregate:

     withSum('orders as total_order_value', 'amount')
     

3. Null Values:

   • Behavior: Aggregates like SUM ignore NULL values, while AVG treats them as 0. MIN/MAX return NULL if all values are NULL.
   • Fix: Handle NULL in your application logic:

     $total = $user->orders_sum ?? 0;
     

4. Overwriting Attributes:

   • Risk: Aliases can overwrite existing model attributes (e.g., created_at).
   • Fix: Use unique prefixes for aliases:

     withSum('orders as order_total_amount', 'amount')
     

5. Complex Relationships:

   • Limitation: Aggregates work best with direct relationships (e.g., hasMany). Polymorphic or nested relationships may require raw queries.
   • Workaround: Use with() with a closure for custom logic:

     User::with(['orders' => function ($query) {
         $query->select(\DB::raw('user_id, SUM(amount) as total'));
     }])->get();
     

Debugging

• Query Logs: Enable Laravel’s query logging to inspect generated SQL:

  \DB::enableQueryLog();
  $users = User::withSum('orders', 'amount')->get();
  dd(\DB::getQueryLog());
  

• Raw SQL: For complex cases, fall back to raw queries:

  $results = DB::table('users')
              ->leftJoin('orders', 'users.id', '=', 'orders.user_id')
              ->select('users.*', \DB::raw('SUM(orders.amount) as total_orders'))
              ->groupBy('users.id')
              ->get();
  

Extension Points

1. Custom Aggregates:

   • Extend the package by publishing and modifying the AggregateServiceProvider to add more functions (e.g., withStdDev).
   • Steps:
     1. Publish the provider: php artisan vendor:publish --provider="Watson\Aggregate\AggregateServiceProvider".
     2. Add your custom method in the boot() method.

2. Global Scopes:

   • Apply aggregate-specific scopes globally:

     class OrderScope implements \Illuminate\Database\Eloquent\Scope
     {
         public function apply(\Illuminate\Database\Eloquent\Builder $builder, \Illuminate\Database\Model $model)
         {
             $builder->withSum('items', 'price as total_price');
         }
     }
     

     Register the scope in your Order model’s $with property:

     protected static $with = ['orderScope'];
     

3. Testing:

   • Mock aggregates in unit tests:

     $user = User::withSum('orders', 'amount')->first();
     $this->assertEquals(100, $user->orders_sum);
     

   • Use DB::shouldReceive() to stub queries:

     DB::shouldReceive('select')
         ->once()
         ->andReturn([/* mock data */]);