framework: I can't eager load a pivot table relation

• Laravel Version: 9.17.0
• PHP Version: 8.1.0
• Database Driver & Version: MySQL - v8.0.27

Description:

Hi everyone

My issues is that i can’t eager load a pivot relation only lazy loading is available.

For example i have the following tables

Products

id	name	barcode_type	barcode	unit_id	cost	price
1	Product 1	code128	000000000001	1	10	12

Purchases

id	warehouse_id	supplier_id	status	date	reference_no	note	total
1	1	1	received	2022-06-17 17:19:34	NULL	NULL	120

Units

id	unit_id	name	code	multiplier
1	NULL	Piece	Pcs	1
2	1	Dozen - 12	D-12	12

And now the pivot table

product_purchase

purchase_id	product_id	unit_id	quantity	cost	subtotal
1	1	2	1	10	120

Models

// Product Model
public function purchases()
{
    return $this->belongsToMany(Purchase::class, 'product_purchase', 'product_id', 'purchase_id')
        ->withPivot('unit_id', 'quantity', 'cost', 'subtotal')
        ->using(ProductPurchase::class);
}

public function unit()
{
    return $this->belongsTo(Unit::class, 'unit_id', 'id');
}


// Purchase Model
public function products()
{
    return $this->belongsToMany(Product::class, 'product_purchase', 'purchase_id', 'product_id')
        ->withPivot('unit_id', 'quantity', 'cost', 'subtotal')
        ->using(ProductPurchase::class);
}


// ProductPurchase Model that extends Pivot
public function unit() // I need to eager load this
{
    return $this->belongsTo(Unit::class, 'unit_id', 'id');
}

Note that i want the unit which the product was purchased with not the default product unit

I want to eager load the unit like this.

This will throw that pivot relation does not exist.

$purchase = Purchase::with(['products.pivot.unit'])->first();

But i can access it via lazy loading like so:

$purchase = Purchase::with(['products'])->first();

foreach($purchase->products as $product) {
    info($product->pivot->unit);
}

But this will produce N+1 queries

This is the desired output for $purchase

{
    "id": 1,
    "warehouse_id": 1,
    "supplier_id": 1,
    "status": "received",
    "date": "2022-06-17 07:16:35 AM",
    "reference_no": null,
    "total": 120,
    "note": null,
    "created_at": "2022-06-17 10:53:46 AM",
    "updated_at": "2022-06-17 06:51:27 PM",
    "products": [
        {
            "id": 1,
            "name": "Product 1",
            "barcode_type": "code128",
            "barcode": "000000000001",
            "unit_id": 1,
            "cost": 10,
            "price": 12,
            "pivot": {
                "purchase_id": 1,
                "product_id": 1,
                "unit_id": 2,
                "quantity": 1,
                "cost": 10,
                "subtotal": 120,
                "unit": {
                    "id": 2,
                    "unit_id": 1,
                    "name": "Dozen - 12",
                    "code": "D-12",
                    "multiplier": 12
                }
            }
        }
    ]
}