Joins and Unions

Joins and Unions

join('customers as c', 'c.id', '=', 'o.customer_id') writes an inner join; leftJoin(), rightJoin() and crossJoin() the others. For a compound ON, pass a closure calling on() and where(), which is where an outer join's right-table filter belongs (LEFT and RIGHT OUTER JOIN). Here is INNER JOIN and the ON Clause's question, what each paid order came to:

Section 3.5.6 as a builder chain, then a UNION ALL activity feedPHP

$paid = DB::table('orders as o')
    ->join('customers as c', 'c.id', '=', 'o.customer_id')
    ->join('order_items as i', 'i.order_id', '=', 'o.id')
    ->where('o.status', 'paid')->groupBy('o.id')
    ->select('o.id', 'c.name')->selectRaw('sum(i.quantity * i.unit_price) as total')
    ->orderByDesc('total');
echo wordwrap($paid->toRawSql(), 95), "\n";
$paid->get()->each(fn ($row) => print(json_encode($row)."\n"));
$reviews = DB::table('reviews')->where('customer_id', 1)->select('created_at as at');
echo wordwrap(DB::table('orders')->where('customer_id', 1)->select('ordered_at as at')
    ->unionAll($reviews)->orderBy('at')->toRawSql(), 95), "\n";

Output

select `o`.`id`, `c`.`name`, sum(i.quantity * i.unit_price) as total from `orders` as `o` inner
join `customers` as `c` on `c`.`id` = `o`.`customer_id` inner join `order_items` as `i` on
`i`.`order_id` = `o`.`id` where `o`.`status` = 'paid' group by `o`.`id` order by `total` desc
{"id":9,"name":"Ben Carter","total":"53.99"}
{"id":7,"name":"Farah Rahman","total":"50.00"}
{"id":3,"name":"Chen Wei","total":"49.00"}
(select `ordered_at` as `at` from `orders` where `customer_id` = 1) union all (select
`created_at` as `at` from `reviews` where `customer_id` = 1) order by `at` asc

The totals match MySQL row for row. joinSub($builder, 'alias', ...) joins a builder as a derived table (Derived Tables and LATERAL), and joinLateral() as a LATERAL one. union() and unionAll() parenthesize each operand, so a trailing orderBy() sorts the combined feed, and unionAll() skips the duplicate check when the halves cannot overlap (UNION, INTERSECT, and EXCEPT).

MENU