Nested Eager Loading with Joins in Laravel

Question

I have the following Tables and their relationships

products Table:

id    product_name    product_price
1     Product A       2 USD
2     Product B       3 USD

components Table

id   component_name    component_price
1    Component A       5 USD
2    Component B       3 USD 

product_component Pivot Table

id component_id       product_id
1  1                  1
2  1                  2
3  2                  2

orders Table

id    order_date
1     "2015-05-06"

order_items TABLE

id order_id component_id  quantity
1  1          1             1
2  1          2             2

The Order Model

class Order extends Model {

    public function items()
    {
        return $this->hasMany('OrderItem');
    }

}

The OrderItem Model:

class OrderItem extends Model {

    public function orders()
    {
        return $this->belongsTo('Order');
    }

}

Product Model

class Product extends Model {

    public function components()
    {
        return $this->belongToMany('Component');
    }

}

Component Model

class Component extends Model {

    public function products()
    {
        return $this->hasOne('Product');
    }

}

Product Component Model

class ProductComponent extends Model {

    public function products()
    {
        return $this->belongsTo('Product')->withPivot();
    }

    public function components()
    {
        return $this->belongsTo('Component')->withPivot();
    }
}

View

    <h3>Order Id : {{ $order->id }} </h3>
    <h3>Order Date : {{ $order->order_date }} </h3>

@foreach($order->items as $item)
    <tr>
        <td>{{ $item->component_name }}</td>
        <td>{{ $item->component_price }}</td>

    </tr>
@endforeach

My Controller:

public function show($id)
    {

        $order = Order::with(array('items' => function($query) 
                    {
                        $query->join('components AS c', 'c.id', '=', 'order_items.component_id');
                    }))
                    ->find($id);

        return view('orders', compact('order'));
    }

I am only able to produce the following report with the above code

Order No : 1
Order Date : 2015-05-06

Component A    5 USD

Component B    3 USD

However, I need the Order Report in the following format with Product details for every Product Component.

Order No : 1
Order Date : 2015-05-06

Component A   

 - Product A   2 USD  
 - Product B   3 USD
   Total       5 X 1 = 5 USD

Component B    3 USD

 - Product B   3 USD
   Total       3 X 2 = 6 USD

I think I am in the correct direction, but need guidance to generate the desired report.

Answer 1

I was able to solve this using the following:

class OrderItem extends Model {

    public function orders()
    {
        return $this->belongsTo('Order');
    }

    public function products()
    {
        return $this->component->belongsToMany('App\Models\Product\Product');
    }

    public function component()
    {
        return $this->hasOne('App\Models\Product\Component');
    }
}

Controller:

$order = Order::with(array('items' => function($query) 
                    {
                        $query->with('products')
                        ->join('product_components AS c', 'c.id', '=', 'product_order_items.component_id')
                        ;
                    }))
                    ->find($id);

View:

<h3>Order Id : {{ $order->id }} </h3>

<h3>Order Date : {{ $order->order_date }} </h3>

@foreach($order->items as $item)
    <tr>
        <td>{{ $item->component_name }}</td>
        <td>{{ $item->component_price }}

        @foreach($item->products AS $product)
            <li>{{ $product->name }}</li>
        @endforeach
        </td>
    </tr>
@endforeach

Answer 2

You can eager-load nested relations as follows:

Order::with('items.products')->get();