php 23 lines · 8 steps

Building an aggregate report with Laravel's query builder

A single fluent query joins orders to customers, applies conditional date filters, and aggregates per-customer revenue.

Explained by highlit
Laravel Intermediate query builder aggregation conditional queries sql reporting
1public function customerTotals(Request $request): Collection
2{
3 return DB::table('orders')
4 ->join('customers', 'customers.id', '=', 'orders.customer_id')
5 ->where('orders.status', 'completed')
6 ->when($request->filled('from'), function ($query) use ($request) {
7 $query->whereDate('orders.created_at', '>=', $request->date('from'));
8 })
9 ->when($request->filled('to'), function ($query) use ($request) {
10 $query->whereDate('orders.created_at', '<=', $request->date('to'));
11 })
12 ->selectRaw('customers.id as customer_id')
13 ->selectRaw('customers.name')
14 ->selectRaw('COUNT(orders.id) as order_count')
15 ->selectRaw('SUM(orders.total_cents) as revenue_cents')
16 ->selectRaw('ROUND(AVG(orders.total_cents)) as avg_order_cents')
17 ->selectRaw('MAX(orders.created_at) as last_order_at')
18 ->groupBy('customers.id', 'customers.name')
19 ->havingRaw('SUM(orders.total_cents) > ?', [$request->integer('min_revenue', 0)])
20 ->orderByDesc('revenue_cents')
21 ->limit(50)
22 ->get();
23}
01 / 01
STEP 01

Walkthrough

Space play ←→ step click any line
Three takeaways
  1. 1The query builder's fluent chain lets you compose complex SQL without hand-writing the whole statement.
  2. 2when() keeps optional filters clean by only applying clauses when a condition holds, avoiding branching logic outside the chain.
  3. 3Aggregate reports pair selectRaw expressions with groupBy and havingRaw to summarize and filter grouped rows.

Related explainers

php 
final class Route
{
    private string $regex;
    private array $paramNames = [];

Compiling route patterns into regex in PHP

routing regex parsing
Intermediate 8 steps
php 
<?php
 
namespace App\Services;
 

Tagged cache invalidation in Laravel

caching cache-tags invalidation
Intermediate 5 steps
php 
final class BitmapGrid
{
    private SplFixedArray $cells;
 

A flat-array bitmap grid in PHP

data-structures row-major-order bitmap
Intermediate 7 steps
php 
namespace App\Http\Filters;
 
use Closure;
use Illuminate\Database\Eloquent\Builder;

Composable query filters with Laravel's Pipeline

pipeline query-builder single-responsibility
Intermediate 7 steps
php 
namespace App\Providers;
 
use Illuminate\Database\Query\Builder as QueryBuilder;
use Illuminate\Database\Eloquent\Builder as EloquentBuilder;

Adding a whereActive macro in Laravel

macros service-provider query-builder
Intermediate 5 steps
php 
<?php
 
namespace App\Reminders;
 

Scheduling reminders across time zones in PHP

timezones datetime immutability
Intermediate 7 steps

Share this explainer

Here's the card — post it anywhere.

Building an aggregate report with Laravel's query builder — share card
Made with highlit — turn any snippet into a walkthrough like this in about a minute.
Explain your code