In Laravel's query builder, how would you build a coffee-shop chain's monthly revenue-per-shop report with a join, aggregates and grouping, keeping shops that sold nothing?

middleshould knowfreq 42%

answer

join from shops, not orders
leftJoin with a join closure
selectRaw with bindings for sums
groupBy every selected column
sum() on a grouped query misleads

basics

~20 s

Start from shops, leftJoin orders with the month and status conditions inside the join closure, selectRaw a coalesced sum, and groupBy the shop columns. A date filter in where() would drop zero-sale shops, and calling sum() returns one number.

solid answer

~30 s

Build the report from DB::table('shops'), because the shops define the rows you want. Use leftJoin('orders', function (JoinClause $join) use ($from, $to) { ... }) and put on('orders.shop_id', '=', 'shops.id') plus the where('orders.status', 'paid') and whereBetween('orders.paid_at', [$from, $to]) conditions inside the join closure; the same conditions in the outer where() would discard the null rows a left join produces for idle shops. Select with selectRaw('coalesce(sum(orders.total_cents), 0) as revenue_cents'), groupBy('shops.id', 'shops.name'), and filter groups with havingRaw('sum(orders.total_cents) > ?', [$floor]). Finish with get(): calling ->sum() on a grouped builder returns only the first group's aggregate.

Build the report from `DB::table('shops')`, because the shops define the rows you want. Use `leftJoin('orders', function (JoinClause $join) use ($from, $to) { ... })` and put `on('orders.shop_id', '=', 'shops.id')` plus the `where('orders.status', 'paid')` and `whereBetween('orders.paid_at', [$from, $to])` conditions **inside** the join closure; the same conditions in the outer `where()` would discard the null rows a left join produces for idle shops. Select with `selectRaw('coalesce(sum(orders.total_cents), 0) as revenue_cents')`, `groupBy('shops.id', 'shops.name')`, and filter groups with `havingRaw('sum(orders.total_cents) > ?', [$floor])`. Finish with `get()`: calling `->sum()` on a grouped builder returns only the first group's aggregate.

code

php · 18 lines

php

<?php

use Illuminate\Support\Facades\DB;

// Misleading: returns only the FIRST group's sum
$oneNumber = DB::table('orders')
    ->where('status', 'paid')
    ->groupBy('shop_id')
    ->sum('total_cents');

// Per-shop sums: select the aggregate and get() the rows
$perShop = DB::table('orders')
    ->where('status', 'paid')
    ->select('shop_id')
    ->selectRaw('sum(total_cents) as revenue_cents')
    ->groupBy('shop_id')
    ->havingRaw('sum(total_cents) > ?', [100000])
    ->get();

go deeper

for a junior

Recall join, selectRaw with a sum, groupBy and get() as the pieces of a grouped report.

for a middle

Explain why value conditions on the optional side of a left join belong in the join closure, and why sum() on a grouped builder misleads.

for a senior

Show how you guard a report against row multiplication from a second join, index-defeating date functions and inclusive range bounds.

for a principal

Weigh building reports on the live query builder against a summary table or a separate reporting store as volume grows.

The report

A coffee-shop chain wants, for September, one row per shop with the shop's name and its paid revenue, including shops that sold nothing that month so a manager can spot them. The data lives in two tables: shops (id, name) and orders (id, shop_id, status, total_cents, paid_at).

Building it step by step

Start from the side that defines the rows. Every shop must appear, so the query starts at DB::table('shops').
Left join the facts. leftJoin('orders', ...) keeps each shop even when no order matches, filling the order columns with NULL.
Put the filter in the join, not the where. A JoinClause extends the query builder, so its closure accepts on() for column comparisons and ordinary where() or whereBetween() for value conditions, which are bound as parameters.
Aggregate with a raw expression. selectRaw() accepts an array of bindings as its second argument; coalesce(sum(...), 0) turns the NULL sum of an idle shop into zero.
Group by every non-aggregated column. groupBy('shops.id', 'shops.name') satisfies strict SQL modes and PostgreSQL.
Filter groups with having. havingRaw('sum(orders.total_cents) > ?', [$floor]) filters after aggregation, which a where cannot do.

php

use Illuminate\Database\Query\JoinClause;
use Illuminate\Support\Facades\DB;

$rows = DB::table('shops')
    ->leftJoin('orders', function (JoinClause $join) use ($from, $to) {
        $join->on('orders.shop_id', '=', 'shops.id')
             ->where('orders.status', '=', 'paid')
             ->whereBetween('orders.paid_at', [$from, $to]);
    })
    ->select('shops.id', 'shops.name')
    ->selectRaw('coalesce(sum(orders.total_cents), 0) as revenue_cents')
    ->selectRaw('count(orders.id) as order_count')
    ->groupBy('shops.id', 'shops.name')
    ->orderByDesc('revenue_cents')
    ->get();

The two traps interviewers look for

A left join undone by where. If the date condition moves to the outer query, as ->whereBetween('orders.paid_at', [$from, $to]), the idle shops' rows carry paid_at = NULL, fail the condition and vanish. The query silently becomes an inner join. Conditions on the optional side of a left join belong in the join closure.

A grouped query ending in sum(). ->groupBy('shops.id')->sum('orders.total_cents') looks like "sum per shop" but it is an aggregate terminal: the builder replaces the select list with sum(...) as aggregate, keeps the group by, runs the query and returns the value from the first result row only. You get one shop's number, presented as if it were the total. Per-group values come from selectRaw plus get().

Related tools on the same builder

Need	Builder call
Compare two columns in a `where`	`whereColumn('orders.updated_at', '>', 'orders.paid_at')`
Join to an aggregated subquery	`joinSub($perShop, 'per_shop', fn (JoinClause $j) => $j->on(...))`
Add one scalar subquery as a column	`selectSub($query, 'alias')` or `addSelect([...])`
Filter groups	`havingRaw('count(orders.id) > ?', [10])`, portable across drivers
Group by an expression	`groupByRaw(...)` with bindings

joinSub is useful when you want to aggregate orders first and then join the result, so that a second one-to-many join such as order_items does not multiply the order rows before summing.

Practical checks before shipping it

Money in integer cents avoids floating-point sums; format at the edge.
Half-open ranges are safer for timestamps than whereBetween, whose upper bound is inclusive: where('orders.paid_at', '>=', $from)->where('orders.paid_at', '<', $nextMonth) inside the join closure.
Avoid whereMonth() for filtering a large table: it wraps the column in a date function, which usually stops an index on paid_at from being used.
Read the SQL with toRawSql() before trusting the numbers, and compare one shop's figure with a hand-written query.

What comes back

The result is a Collection of stdClass rows with id, name, revenue_cents and order_count properties. They are plain rows, not models, so no casts apply: the aggregate arrives in whatever type the database driver returns for it, and code that formats money should cast it explicitly, for example with (int) $row->revenue_cents. Because the rows are ordinary objects, the Collection can be mapped straight into a view model or a CSV export without hydrating a single Eloquent model, which is the main reason reports like this are written on DB::table() in the first place.

## The report A coffee-shop chain wants, for September, one row per shop with the shop's name and its paid revenue, **including** shops that sold nothing that month so a manager can spot them. The data lives in two tables: `shops (id, name)` and `orders (id, shop_id, status, total_cents, paid_at)`. ## Building it step by step 1. **Start from the side that defines the rows.** Every shop must appear, so the query starts at `DB::table('shops')`. 2. **Left join the facts.** `leftJoin('orders', ...)` keeps each shop even when no order matches, filling the order columns with `NULL`. 3. **Put the filter in the join, not the `where`.** A `JoinClause` extends the query builder, so its closure accepts `on()` for column comparisons and ordinary `where()` or `whereBetween()` for value conditions, which are bound as parameters. 4. **Aggregate with a raw expression.** `selectRaw()` accepts an array of bindings as its second argument; `coalesce(sum(...), 0)` turns the `NULL` sum of an idle shop into zero. 5. **Group by every non-aggregated column.** `groupBy('shops.id', 'shops.name')` satisfies strict SQL modes and PostgreSQL. 6. **Filter groups with `having`.** `havingRaw('sum(orders.total_cents) > ?', [$floor])` filters after aggregation, which a `where` cannot do. ```php use Illuminate\Database\Query\JoinClause; use Illuminate\Support\Facades\DB; $rows = DB::table('shops') ->leftJoin('orders', function (JoinClause $join) use ($from, $to) { $join->on('orders.shop_id', '=', 'shops.id') ->where('orders.status', '=', 'paid') ->whereBetween('orders.paid_at', [$from, $to]); }) ->select('shops.id', 'shops.name') ->selectRaw('coalesce(sum(orders.total_cents), 0) as revenue_cents') ->selectRaw('count(orders.id) as order_count') ->groupBy('shops.id', 'shops.name') ->orderByDesc('revenue_cents') ->get(); ``` ## The two traps interviewers look for **A left join undone by `where`.** If the date condition moves to the outer query, as `->whereBetween('orders.paid_at', [$from, $to])`, the idle shops' rows carry `paid_at = NULL`, fail the condition and vanish. The query silently becomes an inner join. Conditions on the **optional** side of a left join belong in the join closure. **A grouped query ending in `sum()`.** `->groupBy('shops.id')->sum('orders.total_cents')` looks like "sum per shop" but it is an **aggregate terminal**: the builder replaces the select list with `sum(...) as aggregate`, keeps the `group by`, runs the query and returns the value from the **first** result row only. You get one shop's number, presented as if it were the total. Per-group values come from `selectRaw` plus `get()`. ## Related tools on the same builder | Need | Builder call | |---|---| | Compare two columns in a `where` | `whereColumn('orders.updated_at', '>', 'orders.paid_at')` | | Join to an aggregated subquery | `joinSub($perShop, 'per_shop', fn (JoinClause $j) => $j->on(...))` | | Add one scalar subquery as a column | `selectSub($query, 'alias')` or `addSelect([...])` | | Filter groups | `havingRaw('count(orders.id) > ?', [10])`, portable across drivers | | Group by an expression | `groupByRaw(...)` with bindings | `joinSub` is useful when you want to aggregate orders first and then join the result, so that a second one-to-many join such as `order_items` does not multiply the order rows before summing. ## Practical checks before shipping it - **Money in integer cents** avoids floating-point sums; format at the edge. - **Half-open ranges** are safer for timestamps than `whereBetween`, whose upper bound is inclusive: `where('orders.paid_at', '>=', $from)->where('orders.paid_at', '<', $nextMonth)` inside the join closure. - **Avoid `whereMonth()` for filtering** a large table: it wraps the column in a date function, which usually stops an index on `paid_at` from being used. - **Read the SQL** with `toRawSql()` before trusting the numbers, and compare one shop's figure with a hand-written query. ## What comes back The result is a Collection of `stdClass` rows with `id`, `name`, `revenue_cents` and `order_count` properties. They are plain rows, not models, so no casts apply: the aggregate arrives in whatever type the database driver returns for it, and code that formats money should cast it explicitly, for example with `(int) $row->revenue_cents`. Because the rows are ordinary objects, the Collection can be mapped straight into a view model or a CSV export without hydrating a single Eloquent model, which is the main reason reports like this are written on `DB::table()` in the first place.

Your report also joins order_items to show cups sold, and revenue suddenly triples. Why, and how do you fix it in the builder?
Joining a second one-to-many table repeats each order once per item, so `sum(orders.total_cents)` counts an order several times. Aggregate each table separately first: build `$items = DB::table('order_items')->select('order_id')->selectRaw('sum(quantity) as cups')->groupBy('order_id')`, then `leftJoinSub($items, 'items', ...)` onto orders so each order stays one row before summing.
Why does having('revenue_cents', '>', 100000) work on MySQL but can fail on PostgreSQL?
`having()` compiles the alias as a column name. MySQL and SQLite accept a select alias in `HAVING`, but PostgreSQL does not, so the same query fails there. `havingRaw('sum(orders.total_cents) > ?', [100000])` repeats the expression and works on each of them.

saying these in an interview costs you the question

Puts the date filter in where() after a leftJoin
Calls ->sum() on a grouped builder expecting per-group totals
Interpolates the month into selectRaw instead of binding it
Starts from orders and expects idle shops to appear
Filters aggregated totals with where instead of having

In Laravel's query builder, how would you build a coffee-shop chain's monthly revenue-per-shop report with a join, aggregates and grouping, keeping shops that sold nothing?

§ answer

basics

solid answer

code

§ go deeper

▾level notes4 levels

▾deep dive~3 min

The report

Building it step by step

The two traps interviewers look for

Related tools on the same builder

Practical checks before shipping it

What comes back

▾common follow-ups2 questions

▾red flags5 flags

The report

Building it step by step

The two traps interviewers look for

Related tools on the same builder

Practical checks before shipping it

What comes back

answer

go deeper