Pivot Columns

Pivot Columns

Working with Intermediate Table Columns

order_items also stores quantity and the unit_price paid. Adding ->withPivot('quantity', 'unit_price') to both definitions puts them on $product->pivot; ->withTimestamps() maintains pivot created_at and updated_at, and ->as('line') renames the pivot attribute.

Reading pivot columns and summing oneSQL

use App\Models\{Order, Product};
foreach (Order::find(2)->products as $book) {
    echo "{$book->title}: {$book->pivot->quantity} x {$book->pivot->unit_price}\n";
}
$top = Product::withSum('orders as sold', 'order_items.quantity')
    ->orderByDesc('sold')->first();
echo "{$top->title}: {$top->sold}\n";

Output

-- select * from `orders` where `orders`.`id` = 2 limit 1
-- select `products`.*, `order_items`.`order_id` as `pivot_order_id`,
    `order_items`.`product_id` as `pivot_product_id`, `order_items`.`quantity` as
    `pivot_quantity`, `order_items`.`unit_price` as `pivot_unit_price` from `products` inner
    join `order_items` on `products`.`id` = `order_items`.`product_id` where
    `order_items`.`order_id` = 2
SQL Queries That Scale: 1 x 44.50
Indexing Deep Dive: 1 x 29.00
Stack Sticker Pack: 3 x 4.99
-- select `products`.*, (select sum(`order_items`.`quantity`) from `orders` inner join
    `order_items` on `orders`.`id` = `order_items`.`order_id` where `products`.`id` =
    `order_items`.`product_id`) as `sold` from `products` order by `sold` desc limit 1
Semicolon Coffee Mug: 6

Eloquent selects the pivot columns under pivot_ aliases, so they cannot clash with product columns, then moves them into the pivot object. wherePivot(), wherePivotIn(), wherePivotNull() and orderByPivot() filter and sort on them, and withPivotValue() filters and fills a column at once. The withSum() column names the pivot table because the aggregate runs against the join.

MENU